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
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
Hi,
listopad is october month in Croatian language So set language in SQL and check below query
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
run above query :
result:
(No column name)
2015-10-10 00:00:00.000
The conversation is with 113 (dd mon yyyy) where mon is listopad (polish lang meaning to english is November so 11th Month) [Unreadable link removed]
Now answer to your questions
1 –> your machine default language might be en-us and for client’s it could be polish.
2 –> Change instance default language https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-default-language-server-configuration-option?view=sql-server-2017 )
3 –> For third question not sure but it could be below.
USE AdventureWorks2012 ;
GO
EXEC sp_configure ‘default language’, 14 ;
GO
RECONFIGURE ;
GO
Thanks.
Using the hint, we know we need to change the language so the datetime gets interpreted correctly
–scroll down and find Croatian
select * from sys.syslanguages
–Or go directly there (after looking at the list first :) )
select * from sys.syslanguages where alias = ‘Croatian’
–hrvatski is the name we need
From there we go
declare @currentlanguage as sysname
select @currentlanguage = @@language
set language ‘hrvatski’;
select convert(datetime, ’10 listopad 2015′, 113);
set language @currentlanguage
Cheers
Thank you all for amazing participation. Some great answers are there. Please keep them coming. I will publish all the comments very soon.
In the clients machine the SQL Language property is set to ‘Polish’, thats why it worked.
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
If the server’s language is Polish, then it will not give an error
set language Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
If the server’s language is English or something else, you may get error
The hint was very helpful because when I googled “listopad” I got (I could not edit my original to add as I posted too quickly)
Listopad, the Croatian month of October
Listopad, the Czech, Polish and Silesian month of November
Thank you for the puzzle
Cheers
I think the customer environment (sql server instance) is not English, it is merely Croatian/some other language.
You need to update the database collation (SQL_Croatian_Cp1250_CS_AS_KI_WI) to get the results
Hi! It’s easy, for those who knows the language!
SET LANGUAGE ‘Polish’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Your language settings don’t know listopad as valid month identifier. Set language to polish or croatian
SET LANGUAGE croatian;
SELECT CONVERT (DATETIME,( ’10 listopad 2015′), 113);
–> 2015-10-10 00:00:00.000
SET LANGUAGE polish;
SELECT CONVERT (DATETIME,( ’10 listopad 2015′), 113);
–> 2015-11-10 00:00:00.000
You can use the Parse/TryParse (SqlServer2012 or higher) function instead:
SELECT TRY_PARSE(’10 listopad 2015′ AS datetime USING ‘pl’) AS [Date&Time]; –polish
SELECT TRY_PARSE(’10 listopad 2015′ AS datetime USING ‘hr’) AS [Date&Time]; –croatian
You can use the SET LANGUAGE { [ N ] ‘language’ | @language_var } clause and give the used language as parameter to stored procedure or use the Parse/TryParse methods…
SET LANGUAGE croatian;
select CONVERT(datetime, ’10 listopad 2015′,113)
Hi Pinal!
I suggest changing your default SQL language to Croatian while working with this client by running this in a query window. (From MSDN web site)
Use Database Name;
Go
Exec sp_configure ‘default language’, 16 ;
Go
RECONFIGURE ;
Go
-October in Croatian
set language Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Hi All,
I suggest you share this blog with your friends and see how many of your friends knows answer of this.
SET LANGUAGE Polish;
SELECT CONVERT (DATETIME, (’10 listopad 2015′), 113);
2015-11-10 00:00:00.000
In Croation, listopad is October, but in a few other languages (such as Polish) it is November. So your client is using one of those languages.
SET LANGUAGE Croatian
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE implicitly overrides DATEFORMAT
Client machine SQL Server language is set as Polish .
SET LANGUAGE ‘Polish’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)