Post reply

Converting SAS dates in a float format to SQL Server datetime values.

  • March 17, 2015 at 3:42 pm

    #315842

    I am importing a couple SAS datasets to SQL Server 2008 for a project. The dates are in a float format, they show up as DT_R8 in SSIS. How can I convert these values to SQL server datetime? I have tried dozens of methods I found on-line with no success, I keep getting 'Arithmetic overflow error converting expression to data type datetime.' errors.

    Thanks!

    Tim C.

  • January 29, 2016 at 4:22 am

    #1855188

    Hi, don't know if you got this fixed and this is pretty old but here goes...

    SAS uses the '19600101' method i.e. the amount of clock ticks after midnight of 1 Jan 1960. would be interesting to find out why they made that choice.

    In any event the code looks like this:

    select dateadd(ss,1752961035.347,'19600101')

    This code results in the date but without the milliseconds:

    2015-07-19 21:37:15.000

    I checked some of the original dates in SAS and the fraction appears to be the milliseconds portion:

    2015-07-19 21:37:15.347

    if you want to automate this in SSIS simply use a "Derived Column" component and manipulate the data like this:

    DATEADD("S",(DT_I8)Some_Date,(DT_DATE)"1960-01-01")

    Remember to use a different name for the "Derived Column Name" otherwise youll have a bad time 😉

  • February 18, 2016 at 1:05 pm

    #1859674

    Thanks! The project was on hold for a while so I'll see if this does the trick now.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply