topic Re: SQL Server and Dates in SAS Procedures

SQL Server and Dates

MikePHFEWIC — Tue, 09 Jun 2009 21:56:11 GMT

I am trying to retrieve SmallDateTime and DateTime data from SQL Server and having trouble getting it to useful formats. Sometimes I need just the dates, sometimes I need the full datetime, sometimes I need to use the SQL Server DateDiff to get hours difference, or minutes or something. Help!

Anyway to do something like:
PROC SQL;
CREATE TABLE XXX AS
SELECT DateDiff(hh,Date1,Date2) as HoursDifference
FROM mySQLTable;

Or can I read the raw dates and apply some inFormat or Format right in the SQL? I can get the difference in a second data step?

Or can I read the raw dates as text then convert them somehow to a datetime format in a second data step?

How do people normal work with SQL Server DateTime values?????

Re: SQL Server and Dates

LinusH — Wed, 10 Jun 2009 16:48:12 GMT

If you want t use SQL Server functions, you need to enclose your query using explicit SQL pass-thru.
You could also use SAS functions/formats, even in the SQL that is reading your SQL Server table(s).
No need for a subsequent data step, unless if you feel more comfortable by doing so.
Use the DATEPART function to convert datetime values to dates.

/Linus

Re: SQL Server and Dates

ChrisNZ — Mon, 15 Jun 2009 23:53:49 GMT

Alternatively, run the SQL in sas and use sas functions:
[pre]
PROC SQL;
CREATE TABLE XXX AS
SELECT intck('hour',Datetime1,Datetime2) as HoursDifference
FROM mySQLTable;