CONVERT NUMERIC TO DATETIME

[lyudmila]

Hi, almost fanny, but
nothing works!
I have a numeirc field and value in this field are (examples):
112300
42101
52301
71901
It's total over 3 million records in this table.
How to convert this field to datetime?
Thank you

 

[cheyney]

in order to do this, you have to decide on a system of converting dates that are ambiguous.

what would 11103 convert to? Jan 11 2003 or Nov 1 2003?

Cheyney

 

[lyudmila]

by analyzing data it's more likely: last two digit is year,
first 1 or 2 are month and middle two digit are dates,
I have padded with leading zero and converted to char
so now example values look like these:
112300
042101
052301
071901

 

[cheyney]

Assuming your table name is Table1 and your 6 digit char column is DateNum:

select  cast(
		case when cast(substring(datenum, 5, 2) as int) < 5 then '20' else '19' end 
		+ substring(datenum, 5, 2) 
		+ '-' + substring(datenum, 1, 2) 
		+ '-' + substring(datenum, 3, 2)
	as datetime)
from table1

Cheyney