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
October 10 in Croatian.
Listopad is croatian name for November and client machine will be having Croatian collation setting.
SET LANGUAGE Croatian;
Hello Pinal
Hope you are doing good and Thanks for such a nice post.Change the language for above mentioned one
DECLARE @Today DATETIME;
SET @Today = ’11/5/2016′;
SET LANGUAGE čeština;
SELECT DATENAME(month, @Today) AS ‘Month Name’;
SET LANGUAGE us_english;
SELECT DATENAME(month, @Today) AS ‘Month Name’ ;
GO
Set language as polish and then execute
SET language CROATIAN;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)
1. Why am I getting this error? How did it work on my client’s machine?
Answer: The error is due to the language settings of your SQL Installation. Since your clients machines language is properly set so its working on client machine.
2. What should I do to get this sorted on my machine?
Answer: Change the Language of you SQL Server and its specific COLLATION
3. What options and settings can you change and what all values can those take?
Answer: Open SSMS > Right click on Server > Click on Properties > Click on Advance in pop up > Change the Default Language to Polish by selecting from the Drop down. > Close the SSMS and Login again and verify by executing the given queries for data.
Thank you for the detailed answer! I really appreciate it how you answered all the three questions with clear instructions.
Main to dewana ho gaya…Thanks Pinal for the puzzle. Keep continuing :)
Hi dev,
1. This is the croatian name for November month. The script which ha been generated from the SQL system installed with default language as croatian. When you tried with us English the conversion may leads to
2. Your test bed need to be sync with the client environment to change the language preference according.
3.
configure the default language option in test bed
In Object Explorer, right-click a server and select Properties.
Click the General settings node.
In the Default language for users box, choose the language in which Microsoft SQL Server should display system messages.
The default language is English.
This is my small knowledge base. Kindly guide me if I’m wrong.
set language CROATIAN
SELECT CONVERT (DATETIME, ’10 listopad 2015′ , 113);
listopad->November
USE AdventureWorks2012 ;
GO
EXEC sp_configure ‘default language’, 16 ;
GO
RECONFIGURE ;
GO
╔════════╦═════════════════════╗
║ LANGID ║ ALIAS ║
╠════════╬═════════════════════╣
║ 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
SELECT convert(varchar, getdate(), 113)
DECLARE @Today date = ’10/19/2016′;
SELECT DATENAME(month, @Today) AS ‘Month Name’;
SET LANGUAGE Croatian;
SELECT DATENAME(month, @Today) AS ‘Month Name’;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
listopad means October then why it is showing 11 as month
Yes I echo with Nidhi, the client database is having different collation and language settings which differs from urs.
Ram Thakur – see https://en.wikipedia.org/wiki/Slavic_calendar Polish and Croatian use the same word for different months
changing the language will run the query as in the example the set language should be croatian..
Syntax :
SET LANGUAGE CROATIAN
SELECT CONVERT (DATETIME, ’10 LISTOPAD 2015′, 113);
1. Why am I getting this error? How did it work on my client’s machine?
Ans: As we usually have default language settings as English on our system, thats why it worked on Client’s machine.
2. What should I do to get this sorted on my machine?
Ans: you should change your language settings to Polish using below mentioned command
SET Language Polish
3. What options and settings can you change and what all values can those take?
Ans: you can refer to this link for setting options :
https://docs.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-syslanguages-transact-sql?view=sql-server-2017
Here is another puzzle on your demand – this one is bit difficult. Let us see if you know the answer of the same or not. https://blog.sqlauthority.com/2016/09/20/sql-server-puzzle-change-date-format-function/
SET LANGUAGE Croatian;
SELECT CONVERT (datetime, ’10 listopad 2015′, 113);
It didnt work for me. I still get the same error!
The reason is LEFT is a string function, and this causes the date to be converted into a string implicitly.
An example of this is SELECT CONVERT( varchar(50), GetDate()) — returns Sep 21 2016 10:30AM