Conversion Error and Language Settings

Question

Conversion Error and Language Settings

SQLEYES

Old Hand

Points: 374
More actions
May 16, 2018 at 5:14 am

#328717

Hi Experts,
I am facing a strange issue where casting varchar to date throws conversion error but if I change the sql User Language setting to 'British English' from 'English' query execute successfully. By default all user set with 'English' as language.
Error:
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.
SQL Code :
CAST
(AA.BAKINGDATE AS date) AS BAKINGDATE

Language Settings :
SQL Instance Language: English (United States)
Database Default Language: English
SQL User Language : English
Any thoughts would be much appreciated.
Regards
IN

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

I'm guessing that your BAKINGDATE column has dates in the format mm/dd/yyyy. Those don't make any sense in a British environment. The solution, of course, is to make BAKINGDATE datetime and not varchar.

John

HanShi SSC-Dedicated Points: 33507 More actions · Answer 2

The most common date format in the US is mm/dd/yyyy, whereas in Europe (including the UK) it's more common to have dd/mm/yyyySo setting the language settings of a login affects the interpretation of a date string, resulting in the string "10/13/18" being correct with the US setting and generating an error with the UK setting. The string "13/10/18" would result in an error with US setting and being correct in UK setting.
So as John Mitchell suggests: use a datetime datatype (in both frontend and database) to store and process a date. Of course this could still lead to unexpected results when you mix UK and US language interpretation (think of entering a date as 04/05/18 --> would this be 4th of May or the 5th of April?)

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQLEYES Old Hand Points: 374 More actions · Answer 3

John Mitchell-245523 - Wednesday, May 16, 2018 5:33 AM
I'm guessing that your BAKINGDATE column has dates in the format mm/dd/yyyy. Those don't make any sense in a British environment. The solution, of course, is to make BAKINGDATE datetime and not varchar.
John

Thanks much John. That makes sense. Unfortunately its coming from source system in ETL process hence beyond my control to change it.

Regards
IN

Thom A SSC Guru Points: 99130 More actions · Answer 4

nadeem161 - Wednesday, May 16, 2018 5:45 AM
John Mitchell-245523 - Wednesday, May 16, 2018 5:33 AM
I'm guessing that your BAKINGDATE column has dates in the format mm/dd/yyyy. Those don't make any sense in a British environment. The solution, of course, is to make BAKINGDATE datetime and not varchar.
John
Thanks much John. That makes sense. Unfortunately its coming from source system in ETL process hence beyond my control to change it.
Regards
IN

I'd suggest giving feedback back to whoever made the ETL process then! Clearly who ever built it forgot that the T in ETL means Transformation and thus just did an "EL" process.

On a different note, you could use a style tag to convert the date. Assuming that they are all in the same format then one of these should work:
--if in MM/dd/yyyy format CONVERT(date,AA.BAKINGDATE,101) AS BAKINGDATE --if in dd/MM/yyyy format CONVERT(date,AA.BAKINGDATE,103) AS BAKINGDATE
If the styles are mixed, quite honestly, you have a much bigger problem.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

SQLEYES Old Hand Points: 374 More actions · Answer 5

Thom A - Wednesday, May 16, 2018 5:51 AM
nadeem161 - Wednesday, May 16, 2018 5:45 AM
John Mitchell-245523 - Wednesday, May 16, 2018 5:33 AM
I'm guessing that your BAKINGDATE column has dates in the format mm/dd/yyyy. Those don't make any sense in a British environment. The solution, of course, is to make BAKINGDATE datetime and not varchar.
John
Thanks much John. That makes sense. Unfortunately its coming from source system in ETL process hence beyond my control to change it.
Regards
IN
I'd suggest giving feedback back to whoever made the ETL process then! Clearly who ever built it forgot that the T in ETL means Transformation and thus just did an "EL" process.
On a different note, you could use a style tag to convert the date. Assuming that they are all in the same format then one of these should work:
--if in MM/dd/yyyy format CONVERT(date,AA.BAKINGDATE,101) AS BAKINGDATE --if in dd/MM/yyyy format CONVERT(date,AA.BAKINGDATE,103) AS BAKINGDATE
If the styles are mixed, quite honestly, you have a much bigger problem.

Thanks much.