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

[Billybob73]

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

BB

[Kurt_Bremser]

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.

[Billybob73]

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

BB

[Kurt_Bremser]

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.

[Billybob73]

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

BB

[Kurt_Bremser]

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.

[Tom]

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?