CONVERT function now expects dates in dd/mm/yyyy? 1

[ookete]

I use the Convert function as follows (a little example):

SELECT CONVERT(datetime,MyDateField,101)

This has always worked. Now if I wanted the British format, I'd put a 103 in the end. That also worked in the past.

BUT the weird thing now is that the CONVERT function is expecting MyDateField in dd/mm/yyyy format instead of mm/dd/yyyy as was always the case before. It really has nothing to do with the 101, 103, etc as that is the output. It has to do with how the CONVERT function interprets the incoming date.

Is there a setting or toggle that anyone knows of to tell CONVERT which date format to expect?

Thanks!

 

[gmmastros]

This blog that I wrote completely explains the issue.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/setting-a-standard-dateformat-for-sql-se

It used to work... now it doesn't... you (or someone else) probably changed the default language for your login.

BTW, I strongly encourage you to STOP storing dates in varchar columns. If you had used DateTime, this wouldn't be a problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ookete]

George, thank you for the quick response. I bet that is exactly what happened, now I know where to look.

Yes, we do store our dates as DateTime, but this is a special case where we are dealing with a feed that has dates entered as text.

 

[gmmastros]

It really has nothing to do with the 101, 103, etc as that is the output.

Actually, this is not correct. The output here is DateTime. According to Books On Line:

style

Is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), [!]or to convert character data of known date or time formats to datetime or smalldatetime data[/!]; or the string format used to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). When style is NULL, the result returned is also NULL.

Notice the parts in red. Turns out you were using it correctly the whole time. There must be something else going on that you're not showing. Perhaps you really do have a bad date in your feed. I suggest you use the IsDate function to look for the bad data.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom