Sql Server DATETIME format incorrrect

Question

Hello Folks,

I have a field type varchar in sql server. It contains data like "010109" etc.

When I try to convert this to DATETIME it returns a value "9 Jan 2001" when the actual value should be "1 Jan 2009".

Does anybody have a fix for this?

Thanks for your help.

Answer 1

I thought there would be some conversion format so you could put:

select(convert(datetime,'010109',<some magic number>))

and get the result, but I can't seem to find one that gives the right date :(

This works, but is pretty nasty:

declare @dt varchar(6)
select @dt = '010109'
select convert(datetime,RIGHT(@dt,2) + SUBSTRING(@dt,3,2) + LEFT(@dt,2))

Yeesh

Answer 2

When you type date in the format of 'xxxxxx' it seems that SQLServer assumess it is an ISO format yymmdd and as such it is not affected by the SET DATEFORMAT

I was aware of 2 such formats - so called safe formats

• ISO: yyyymmdd
• ISO8601:yyyy-mm-ddThh:mi:ss.mmm

but it seems that yymmdd is also ISO - check BOL Date and Time Styles - format 12

That would explain why the solution posted by Scorpio did not work

You can use the solution provided by butterchicken with the format specification (12) to be on a safe side:

declare @dt varchar(6)
select @dt = '010109'
select convert(datetime,RIGHT(@dt,2) + SUBSTRING(@dt,3,2) + LEFT(@dt,2),12)

If possible I would be ideal if you could change the column to datetime to avoids similar surprises in the future

Answer 3

SQL Server is expecting the date to be in the format YMD

So if your string was like this '090101' you would get the proper date.

select(convert(datetime,'090101',104))

So you will have to substring out the parts and run the convert.