March 17, 2015 at 3:42 pm
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
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
Thanks! The project was on hold for a while so I'll see if this does the trick now.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy