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
/*
You are getting this error because the value ”10 listopad 2015′ is not a recognized date value in your system.
This is a recognized date value on your client’s system.
Per your hint it relates to language…so language must be set first.
*/
–Have user run this on their system.
SELECT
@@LANGUAGE;
–Set your session to that language (Can be Croation or hrvatski in this case.)
SET LANGUAGE hrvatski;
SELECT
CONVERT (DATETIME, CAST(’10 listopad 2015′ AS VARCHAR), 113);
To get the correct output you need to SET LANGUAGE POLISH and it will return the correct date format.
when restoring the database to your own machine you have to use collation SQL_Croatian_Cp1250_CI_AS_KI_WI
select convert (datetime, ’10 listopad 2015′, 113)
— Fails as listopad is not a recognised month
— My instance is set to English (United States)
select @@LANGUAGE — us_english
— A quick google translate shows that listopad is Polish for November
— So, the client must have a Polish SQL Server instance (they are probably Polish)
— Dependant upon what you want to achieve (one-off, more to this project, …), a number of approaches can be taken
— Use replace
select convert (datetime, replace(’10 listopad 2015′, ‘listopad’,’November’), 113)
— Convert the language to Polish for this session and run
set language Polish
select convert (datetime, ’10 listopad 2015′, 113) — 2015-11-10 00:00:00.000
— Convert back
set language us_english — Or whatever @@LANGAGE provided
— If all you want is the date in date type with a switch of language
declare @language varchar(20)
declare @myDate date
select @language = @@LANGUAGE
— set the date using a Polish language convert
set language Polish
select @myDate = convert (datetime, ’10 listopad 2015′, 113)
— set the language back and show the results
set language @language
select @myDate
— Amend the SQL Server default language via SSMS
Hello Pinal,
I have learn sql concept from your blogs thanks you for writing blogs. :)
I have received your email notification about this puzzle and looked into it and have found issue is that on client machine default language set is different than what is set in your machine. I read this article on MSDN library to set language. https://docs.microsoft.com/en-us/sql/t-sql/statements/set-language-transact-sql?view=sql-server-2017
By setting language on my machine I am able to execute above statement without error.
DECLARE @Today VARCHAR(20);
SET @Today = ’10 listopad 2015′;
SET LANGUAGE polski;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
I am assuming the default Code Page character encoding on your clients SQL Server is not the standard UTF encoding. I am assuming this is why its converting the listopad string into the datetime format.
Hi Pinal,
Please find the answers as given below
Why am I getting this error? How did it work on my client’s machine?
Answer : You are getting error because the session language setting is other the CROATIAN while at client’s machine it is set to CROATIAN.
What should I do to get this sorted on my machine?
Answer: You must set the session language to the desired one
e.g.
SET LANGUAGE croatian;
select convert (datetime,’10 listopad 2015′,113)
another example where setting the language to italian.
DECLARE @Today DATETIME;
SET @Today = ’10/11/2007′;
SET LANGUAGE italian;
SELECT DATENAME(month, @Today) AS ‘Month Name’;
SET LANGUAGE us_english;
SELECT DATENAME(month, @Today) AS ‘Month Name’ ;
GO
The setting of SET LANGUAGE is set at execute or run time and not at parse time.
SET LANGUAGE implicitly sets the setting of SET DATEFORMAT.
What options and settings can you change and what all values can those take?
Answer : Only set language property. It takes language as parameter or direct language name as input.
SET LANGUAGE { [ N ] ‘language’ | @language_var }
for more details details you may refer to sys.syslanguages
Thanks
Vijay Soni
Dear Pinal
Thanks for educating us.
You are Guru.
visit
https://en.wikipedia.org/wiki/Croatian_months
It’s a different locale issue.
Solution:
set language polish;
select convert(datetime, ’10 listopad 2015′, 113);
In my earlier comment I gave you the List of Months
Now
SET LANGUAGE Polish
and proceed
Hello Pinal,
Thank you for this puzzle. It seems that your system’s effective language is not set to the same as your client’s system, which I believe is Croatian.
So, in order to resolve you would need to retrieve the same language package and enable it in SSMS under Tools > Options > Environment > International Settings, and then enable Croatian.
1. Your local language is set differently than the client’s. Yours doesn’t understand Listopad as being the 11th month, just like his won’t understand November as being the same.
2. Change the language in your local environment (changing to Czech with recognize Listopad as the 11th month).
3. You can use SET LANGUAGE to change a session’s language, or change it globally in the server’s properties.
Collation settings difference
Hey Pinal
Listopad is the equivalent of November in polish.
A simple search in google showed that ☺
I think it was because the default language (sp_defaultlanguage) was set to Italian or something non-English. You can replicate this using the “SET LANGUAGE ” as well.
Is it something related to database collation.
After a bit of digging I found that 113 is for European standard which helps to understand the input format better for European region and that the default language for the login was changed to Italian or some European language.
Listopad is October in Croatian (ref:https://en.wikipedia.org/wiki/Croatian_months), therefore, is your customer has regional setting in Croatia, the query should succeed in execution.
Thank you all – we have over 100+ amazing comments already – I will publish it in the next day or two. Trust me, there is so much learning here that you all will love it. Please continue the knowledge and leave comment with your answer.
Listopad, the Croatian month of October
Listopad, the Czech, Polish and Silesian month of November
Change dafault server/database or table language to croation or czech or polish.
Since your db/server language was not croation you were getting error.