Converting A String to Date Datatype

Question

User-266149547 posted

I need to convert a number field that contains a date in the following format: (mm/dd/yy) to an actual date datatype.

For example,

number field - 60181 converted to date field 06/01/81

I tried just by changing the datatype from number to date, but the conversion is wrong, it gives me 10/6/2064.

Thanks for any help.

Answer 1

User77042963 posted

SELECT dtNum,

 DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) AS NewDate

,FormatDateTime( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2))   ) as  'MM/DD/YYYY'

,Format( DateSerial(RIGHT([DtNum],2),LEFT([dtNum],LEN(dtnum)-4), LEFT(RIGHT([dtNum],4),2)) ,'MM/DD/YY'   ) as 'MM/DD/YY'

FROM Table1;

Answer 2

User-266149547 posted

Thank you.  That worked perfectly.

Answer 3

User-1199946673 posted

And how would you convert 12181?

is this 12/01/81 or 1/21/81?

Answer 4

User-266149547 posted

That is a good question. 

But I believe 12181 would be 1/21/81

and 120181 would be 12/01/81.