I am preparing for a professional tour of Europe wherein I am planning to meet a number of customers as part of consulting and performance tuning assignments that I have agreed in early October. I am already swamped with the requests and I think the learning experience is second to none when one gets to work on global projects. Let us an interesting puzzle.
As I was preparing for this assignment, one of the customers sent me a small subset of their database code with some data and said they wanted to me to see why the queries were slow and what can be done to work with them. This was an interesting assignment and I went about restoring the same on my machine and was looking at the code. There was a place in the code which had few datetime columns and the stored-procedure was trying to convert the same. I am just taking one of the values stored to show what I was getting:
SELECT CONVERT (DATETIME, '10 listopad 2015', 113);
As you can see, it was a strange piece of datetime that was stored. It was giving me an error as:
Msg 241, Level 16, State 1, Line 15
Conversion failed when converting date and/or time from character string.
I wrote back to the customer to understand what this was. And I immediately got a reply with a screenshot as shown below:
This is when I realized I was doing something fundamentally wrong. How can this work on my client’s machine? A little bit of digging and it was a learning for me.
Puzzle:
- Why am I getting this error? How did it work on my client’s machine?
- What should I do to get this sorted on my machine?
- What options and settings can you change and what all values can those take?
Giveaway hint: Croatian months
Please leave a comment with valid answers to this puzzle. I will publish answers in the future blog post with due credit to the correct answer.
Reference: Pinal Dave (https://blog.sqlauthority.com)
188 Comments. Leave new
1) the language for the database instance was set to English?
2) Use ‘SET LANGUAGE Croatian’ or the language the customer has set on thier database instance (make sure change the language back !!!!!!)
3) this page has a list of languages that can be set – https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-syslanguages-transact-sql?view=sql-server-2017
Confused though, the ‘listopad’ is November (month 11) in Polish, in Crotian its October (month 10) – https://en.wikipedia.org/wiki/Slavic_calendar. The clients machine could be set to Polish or Crotian.
Off to lie down in darken room for a while too much learning and excitment for today……
Hello All,
I have already specified hint in this blog post. :-) Lots of people are asking me for more help to solve the puzzle but the answer is already there.
Hi Pinal, Listopad is Polish name of November, no Croatian.
It seems to depend on SQL server connection language property, if You connect with language=POLISH this select will pass like Your client note
The language of database is not Croatian, that’s why you got the error.
When the connection is done you can set the language. I. E.
SET LANGUAGE Croatian
SELECT CONVERT(DATETIME, ’10 listo pad 2015′,113)
Thanks Pinal
I love the puzzle.
Regards
Antonio
Or change de default language in the server to Croatian with so_configure ‘default language’, @language_value
The problem why it is working on your client machine and not your machine is the language set to your machine sql server is other than “Croatian” and sql Convert function uses servers datetime format that is different for each language
So to solve this problem and make it work on your machine you need to change the language of your server
Following statement should do the job,
set Language Croatian;
Select Convert(DateTime, ’10 listopad 2015′, 113)
1. Your getting this error because your machine doesn’t know what listopad is. The clients machine understands what listopad is.
2. You need to SET LANGUAGE POLISH for this to work on your machine.
3. You can find out the different languages supported by using select * from sys.syslanguages.
It’s too easy ! :)
SET LANGUAGE ‘Croatian’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
listopad = Polish: November;
So a
set language ‘Polish’
lets run you the convert
1. Because on your PC windows Locale and Collation are different. Your client is using Polish local and corresponding default Polish Collation.
2. Alter database and change Collation for Polish language. You can change Collation at column level.
Pinal,
That is the issu with europe language collation..novembet is in europe is listopad
Set Language ‘hrvatski’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
I just put in a SET LANGUAGE line prior to convert and it worked but my result was one day off. ??
SET LAMGUAGE ‘Croatian’
SELECT CONVERT(DATETIME, ’10 listopad 2015′, 113);
Result was: 2015-10-10 00:00:00.000
oops that should LANGUAGE
Hello,
Because language is different in your client’s machine than yours.
You need to set language before running a query.
SET LANGUAGE polish; –It should be set to Polish to match the result with given screen shot.
–SET LANGUAGE croatian; –It should be set to Croation if I follow your hint but it gives different result.
SELECT CONVERT (datetime, ’10 listopad 2015′, 113)
Thank you,
Hatim
Hi,
you just need to set the language by using following statement
set language Croatian
and then run the given statement.
select convert(datetime,’10 listopad 2015′,113)
but I observed I am getting listopad as october, not november as shown in your snapshot.
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Hello Dave, Here are the answers for puzzle
Why am I getting this error? How did it work on my client’s machine?
1. You got this error since your DB language/Collation is US_English
2. This was worked on your client machine because of your client’s DB or windows locale Collation/Language settings is Croatian.
What should I do to get this sorted on my machine?
1. You need to change your language settings to get this processed in your machine. You can do it using “SET LANGUAGE ” option.
What options and settings can you change and what all values can those take?
Here is the script that can be used to get the result in your machine.
Changed language setting to hrvatski.
Croatian
———————–
2015-10-10 00:00:00.000
(1 row(s) affected)
Changed language setting to us_english.
————————————————
Have a nice day !!!
Thanks,
Reena S
Hello Dave, Here are the answers for puzzle
Why am I getting this error? How did it work on my client’s machine?
1. You got this error since your DB language/Collation is US_English
2. This was worked on your client machine because of your client’s DB or windows locale Collation/Language settings is Croatian.
What should I do to get this sorted on my machine?
1. You need to change your language settings to get this processed in your machine. You can do it using “SET LANGUAGE ” option.
What options and settings can you change and what all values can those take?
1. Here is the script that can be used to get the result in your machine.
SET LANGUAGE CROATIAN ;
select CONVERT (DATETIME, ’10 listopad 2015′ , 113) Croatian;
SET LANGUAGE US_ENGLISH ;
Changed language setting to hrvatski.
Croatian
———————–
2015-10-10 00:00:00.000
(1 row(s) affected)
Changed language setting to us_english.
Thanks,
Reena S
set language Croatian
select CONVERT (DATETIME, ’10 listopad 2015′, 113);