Playing with Datetime became a puzzle when one conversion worked for a customer but failed for me. The input was unchanged.
I was preparing for a European consulting tour when a customer sent code and data about slow queries. I restored that subset on my machine. One stored procedure contained this conversion:
SELECT CONVERT(datetime, '10 listopad 2015', 113);
My session reported a character-to-date conversion failure. The different outcome was the clue. It didn’t prove that the customer’s data was invalid.
The original challenge
- Why does this expression fail in one session and succeed in another?
- Which setting could make it work on my machine?
- What values can that setting take, and what should an application rely on?
My hint was Croatian months. Try the puzzle before reading the comments. A month spelling can occur in more than one language with different meanings. A successful conversion alone doesn’t establish the intended date.
Use a separate test connection for experiments. Restore any changed session setting afterward. Share your reasoning without assuming another reader’s language settings.
Related reading
A successful conversion is not proof of the intended date, it is one outcome under particular session rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
189 Comments. Leave new
It may cause of creation language environment setup on client machine.
SET LANGUAGE Croatian;
select convert(datetime,’10 listopad 2015′,113)
Nice.
set language Polish
select convert(datetime, ’10 listopad 2015′, 113)
–This will also result as expected ,SET LANGUAGE Croatian; is best way to do it , but in Czech and Polish listopad means November – rather than October in Croatian – perhaps because the leaves stay on the trees longer in those countries.
write string date with complete format ‘2017-05-19 00:00:00’ HH:MM:SS at least or with ‘.000’
hello sir, You have been providing great content, we indebted you a lot.
“Sounds like a busy but rewarding trip—bring on the puzzle!”