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
Listopad is a Polish word which means November. So if i run below query on my machine then it is similar to given query-
SELECT CONVERT (DATETIME, ’10 November 2015′, 113);.
So the answer is error was because of culture set to client machine.
Thanks,
Anand
Set language Croatian;
Select convert(Datetime,’10 listopad 2015′,113)
The listopad gives the Croatian month name.
=> Why am I getting this error?
– By default, the date format for SQL server is in U.S. date format MM/DD/YY.Your sql server language selected as ‘us_english’
SELECT @@LANGUAGE —returns=>us_english
–works
SELECT CONVERT (DATETIME, ’10 November 2015′, 113);
— fails
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
GO
How did it work on my client’s machine?
=>on client’s machine he installed localized version of SQL Server with default language ‘Chez’.
(2)What should I do to get this sorted on my machine?
– You should set correct language.The strange word ‘listopad’ is name of ‘November’ in Czech language.
SET LANGUAGE N’Czech’;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE only sets the language for the duration of a current session.
(3)What options and settings can you change and what all values can those take?
– run ‘dbcc useroptions’ it will display all option and values.
SET LANGUAGE N’English’;
dbcc useroptions
SET LANGUAGE N’Czech’;
dbcc useroptions
Hi Pinal,
I am a big fan of this blog and have been benefited many times from your postings. You have already given the sufficient clue. However, the explanation is as under:
It is because of regional and language settings of the server/system on which the sql server was installed and the language chosen at the time of installation.
As in your case the language set at your client’s end was croatian and hence it was accepting ‘listopad’ as its equal to October in us_english.
select @@LANGUAGE — can be used to check the language that is set in sql server currently
SET LANGUAGE croatian; — can be used to set sql server language to croatian
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113); — now this statement will work as expected
SET LANGUAGE us_english; — language can be reverted back to us_english using this statement
Hope the above helps. Just let me know if any further clarification required.
set language Czech;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
go
set language Polish;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
go
set language Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
go
— P.S.
select * from sys.syslanguages where months like ‘%listopad%’
Wrong language set?
1)
a)Why am I getting this error?
b)How did it work on my client’s machine?
Ans a) : I checked on server properties where language is set to English
Ans b) : On Client Machine,it might be selected Georagian Calender during Installation or they may have changed default language.
2) What should I do to get this sorted on my machine?
Ans:
SET LANGUAGE ‘Croatian’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
3)What options and settings can you change and what all values can those take?
And :Click Instance name > Properties> Advanced > Miscellaneous > Default Language : Croatian
Eager to know for new possible answer.
Thanks ,
Vaibhav Shukla
Listopad is Polish for “November” You would have to tell SQL Server to use the Polish Language, and then run the query. Something like;
SET LANGUAGE Polish SELECT Convert(datetime, ’10 listopad 2015′, 113) AS ‘English Date’
SET LANGUAGE Polish SELECT DATENAME(month, Convert(datetime, ’10 listopad 2015′, 113)) AS ‘Month Name’
Probaly your sql does not support the Croatian style or language. Change your language to whatevever language the Croatian are using. Guess it worked in your client because he or she was Croatian.
This issue is related to localisation, since TSQL will use the settings assigned on the machine.
So the client has his language set to Croatian (hrvatski) which will understand the “long format date”. You dont.
To allow this to work on any machine then we can use the SET LANGUAGE Croatian; option.
HI Pinal,
The issue was in the language, you have to set the language in sql server using following command,
SET LANGUAGE [Language name];
you can find out the language names from sys.syslanguages table.
in this scenario we needed get the Croatian months,
so first we need to set the language as hrvatski(select * from sys.syslanguages
where alias like ‘%Croatian%’)
SET LANGUAGE hrvatski;
then run your command.
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Ans. In your pc language is English but in client pc it is polish language so it is working in client machine.
You need to change default language from english to polish for instance.
Might be related to locale or localization settings of SQL server instance.
Hi Pinal,
The catch is we have to specify our english calendar to get the proper result.
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113); — It is for croatia.
SELECT CONVERT (DATETIME, ’10 March 2015′, 113); — SQL Server gives result for this as : 2015-03-10 00:00:00.000
Hi,
`listopad` means October in Croatian language.
1) Why am I getting this error? How did it work on my client’s machine?
On client’s machine default language may be Croatian, so it did not throw any error. On your machine default language would be english. To change configuration: EXEC sp_configure ‘default language’, 2 ; Ref: https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-default-language-server-configuration-option?view=sql-server-2017
2) What should I do to get this sorted on my machine?
Either above option to change configuration or use SET LANGUAGE option https://docs.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql?view=sql-server-2017 ) to change per session basis.
3) What options and settings can you change and what all values can those take?
SET LANGUAGE takes any language name from select * from sys.syslanguages, I guess same goes for config
SET LANGUAGE CROATIAN
SELECT CONVERT (datetime, ’10 listopad 2015′, 113)
Listopad is a Croatian language set this language then try it…….
Keep smiling :)
Chiranjeevi
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)
==> 2015-11-10 00:00:00.000
SET LANGUAGE Croatian
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)
==> 2015-10-10 00:00:00.000
The error Is due to regional language settings right.
Hi Pinal,
Below are the findings
1. Why am I getting this error? How did it work on my client’s machine?
A.) While Installing SQL Server the language set by default is English , based on the user location the user can set his language ,
Therefore in the above case your clients default Language is Czech whereas the language set by You is English,
Since the language set by you is English it is not able to read the date Format
2. What should I do to get this sorted on my machine?
A.) If you want this issue to be solved only for a Particular Query then you can set your language and run the below query
SET LANGUAGE czech
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
3. What options and settings can you change and what all values can those take?
You can run the below command and execute Reconfigure to set your language
Exec [sys].[sp_configure] @configname , @configvalue
0 English
1 German
2 French
3 Japanese
4 Danish
5 Spanish
6 Italian
7 Dutch
8 Norwegian
9 Portuguese
10 Finnish
11 Swedish
12 Czech
13 Hungarian
14 Polish
15 Romanian
16 Croatian
17 Slovak
18 Slovenian
19 Greek
20 Bulgarian
21 Russian
22 Turkish
23 British English
24 Estonian
25 Latvian
26 Lithuanian
27 Brazilian
28 Traditional Chinese
29 Korean
30 Simplified Chinese
31 Arabic
32 Thai
33 Bokmål
Or else By GUI go to Propertiesïƒ Advance Tab and Change the Language Settings as per requirement
set language ‘Croatian’
SELECT CONVERT (datetime, ’10 listopad 2015′, 113)