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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The same way. A neg date is one before Jan 1, 1960.
Is it a SAS date or a datetime?
Is it a SAS date or a datetime?
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
DATEADD(SECOND, date_of_birth, '19600101') AS BirthDate
ASKER
thany you!
ok now I'm a little bit confused,
so what are these values then?
-58438
2647911
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
1960-01-31 15:31:51.000 -- 2647911
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
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.
I'd need to see the exact code to try to figure that one out.
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
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.
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.
ASKER
thanks I got it, it was crap data.And the rest converted correctly.
ASKER