topic Unable to convert SQL server datetime 1960 values to the actual date time values in New SAS User

Unable to convert SQL server datetime 1960 values to the actual date time values

Billybob73 — Fri, 17 Jan 2020 11:52:25 GMT

Hi,

I'm query-ing an MS SQL server tabel from SAS.

In SQL Server there is a datetime field which has only got date vaules in 1960, for instance :

1960-01-01 05:40:53.000

1960-01-01 05:45:27.000

I want to convert these to date values

When I run implicit code this works :

put(<datetime field sql server>, ddmmyy10.)

This way 01JAN1960:05:52:03.000 is converted to 31/10/2017, which is correct.

But I would not know how to convert this in explicit code . Which SQL server function should I use ?

The datetype SQL Server uses is DATETIME.

Thanks !!

Rgds

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Kurt_Bremser — Fri, 17 Jan 2020 12:02:39 GMT

Microsoft uses the same base (count of days) for dates and datetimes. Basically, everything is a datetime value, with pure time values as fractions in the range 0 to 1.

SAS uses counts of seconds for datetimes and times. What I would do is simply assign a date format to the variable after import.

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Billybob73 — Fri, 17 Jan 2020 12:34:38 GMT

Hi Kurt,

Thanks for your answer. I cannot do this implicitely. The thing is that i'm writing code to improve performance so I just have to find the solution on the SQL server side. Would you know the SQL server function to do this ? I've been googling a lot but cannot find this solution.

Thanks

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Kurt_Bremser — Fri, 17 Jan 2020 12:39:01 GMT

Has nothing to do with performance. You get the correct value from the SQL Server, it just needs the correct format, and that is done on the SAS side in fractions of a second, or no time at all with a format= in SQL while you load the data.

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Billybob73 — Fri, 17 Jan 2020 12:50:03 GMT

Kurt

It has indirectly. I need the conversion in a broader perspective. It's part of a huge query.

Explicit is always faster than implicit. There's a lot of transformations and conversions in the original implicit code.

I want the SQL server database to process. Therefore I'm transforming all of the SAS functions to SQL server functions.

Thanks

Rgds

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Kurt_Bremser — Fri, 17 Jan 2020 16:55:00 GMT

If you need help with implementing your logic in MS SQL Server, you should ask for it in a Microsoft-oriented forum. I fear we here can't give much help with that SQL dialect.

Re: Unable to convert SQL server datetime 1960 values to the actual date time values

Tom — Fri, 17 Jan 2020 17:06:03 GMT

Your question does not make much sense.

Are you saying that you have a number that Oracle thinks is some time on January first 1960 and you want to convert it to a date in the year 2017? What is the logic for this conversion?

Is it possible you loaded a SAS date value into an Oracle datetime variable without the proper conversion of units? Can you go back and fix the process that loaded the value originally instead of trying to fix the Oracle data after the fact?