Link to home
Start Free TrialLog in
Avatar of elucero
elucero

asked on

Converting sas date into SQL server

I'm trying to convert the following dates into sql from sas, both are coming up incorrect.  Any suggestions?

CASE WHEN date_of_birth <0 THEN DATEADD(ss,21914,CAST(ABS(date_of_birth) AS DATETIME))
ELSE DATEADD(ss,1893369600,CAST([date_of_birth] AS DATETIME) ) END as BirthDate
date_of_birth in('-58438','2647911')

date_of_birth   result
-58438             2059-12-31 06:05:14.000
2647911          9209-09-25 00:00:00.000

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of elucero
elucero

ASKER

Thanks that gives me 1800-01-01, how would I convert -58438 and 2647911
The same way.  A neg date is one before Jan 1, 1960.

Is it a SAS date or a datetime?
Avatar of elucero

ASKER

it's a datetime, so -58438 is about 160 years subtracted from 19600101, so it would be 1800-01-01 and 2647911 is 7254 years added on to 19600101, so these 2 values are trash data.  I'm scrubbing data from a large sas file and these were my min and max birthdate values.  
OK, a datetime is # of seconds, so:

DATEADD(SECOND, date_of_birth, '19600101') AS BirthDate
Avatar of elucero

ASKER

thany you!
ok now I'm a little bit confused,
so what are these values then?  
 -58438
2647911

1959-12-31 07:46:02.000 -- -58438
1960-01-31 15:31:51.000 -- 2647911  


Avatar of elucero

ASKER

thanks,
they are the only 2 values I'm getting when I use DATEADD( SECOND, date_of_birth, '19600101') AS BirthDate

the values -58438 and 2647911   are my min and max date_of_birth values

I have other date_of_birth values
like
 -1152
6980
-4639
Their all coming up at 1959-12-31 07:46:02.000  or 1960-01-31 15:31:51.000 
That doesn't make sense, something's not right.

I'd need to see the exact code to try to figure that one out.
Avatar of elucero

ASKER

--source table starts off as the date_of_birth being a float
Declare @birthtable as table (date_of_birth float)
Insert into  @birthtable
select '-1152'
union
select '6980'
union
select '-4639'
union
select '-58438'
union
select '2647911'



select date_of_birth, dateadd(day,date_of_birth,'19600101'),dateadd(second,date_of_birth,'19600101'),
--the final step is I have to load into a table that's a varchar(100) in this format
convert(varchar(100),(dateadd(second,date_of_birth,'19600101')),121)
from @birthtable
I'm not sure what else you want me to do?  The date should be displaced from Jan 1, 1960.  Neg numbers are before that date, positive numbers after.

As to what your specific data in the table is, I can't really know.  That's an internal data issue for you to resolve, I can't help with that.
Avatar of elucero

ASKER

thanks I got it, it was crap data.And the rest converted correctly.