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 Sir,
It is really interesting puzzle which learned me new things.
You get this conversation error because default language setting of SQL server is British English where date is stored in European format.
You need to use SET LANGUAGE OPTION to set it European.
Do let me know if I am wrong.
1)
You are getting this error because your language is probably english or latin set in your SQL Login. The current language does not know what listopad is.
2)
If you wanted to get this converted without changing the language of the SQL Login, change the statement to use instead:
DECLARE @DEFAULT_LANGUAGE NVARCHAR(256)
SELECT @DEFAULT_LANGUAGE = @@LANGUAGE
SET LANGUAGE ‘hrvatski’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE @DEFAULT_LANGUAGE
3)
ALTER LOGIN with DEFAULT_LANGUAGE = Croatian
ALTER LOGIN with DEFAULT_LANGUAGE = English
syslanguages view contains all possible languages
I saw the word and recognized it as the Russian word for leaves falling or for November. I assume it is the language setting on your clients SQL server. The language setting would determine the date time formats. I believe that is the reason why it worked on your client’s machine, but not on yours.
This would work:
SET LANGUAGE ‘Czech’;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
This is nice, I’ve never tried it before… just know the word “listopad”. I’ll have to see if my coworkers can figure this out!
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)
It is different culture, we can use as follows to run conversion without error
SET LANGUAGE Polish
SELECT CONVERT(datetime, ’10 listopad 2015′, 113)
It seems the client machine has Croatian as the language while yours probably English.
So you have 2 options either change your machine setting or declare in your query script that the language is Croatian.
I don’t think I understand number 3. All options to do what?
In my view i like to use numbers more than strings less typos less fuss plus converting steings means waist is CPU lol.
Just search for the date by numbers better.
That’s more universal and even of the system was in Japan it will still work properly.
SET LANGUAGE hrvatski;
SELECT CONVERT( DATETIME, ’10 listopad 2015′, 113)
Set language Polish
Select convert (datetime ’10 listopad 2015′, 113)
— it will work now. Default language set is english or us_english. You can also change default language from database properties.
Q: Why am I getting this error? How did it work on my client’s machine?
A: Given error occurred due to machine Language. We generally work with English language & as per client code provided it is Croatian.
Q: What should I do to get this sorted on my machine?
A: We need to change session level language setting By using below code.
[SQL]SET LANGUAGE Croatian;[/SQL]
Q: What options and settings can you change and what all values can those take?
A: For options we can refer sys.syslanguages table & for values we can compare months/shortmonths column in it.
[SQL]Select * From sys.syslanguages D Where D.alias In (‘Croatian’,’English’)[/SQL]
Use the “SET LANGUAGE POLISH” command once for the session.
You will have to set language to specific language like for that in locla machine it will work with :
SET LANGUAGE Polish;
Sir,
This is due to difference in collation schemes from server to client machine.
As In Macedonian, Listopad is also the (archaic) name for October but in Polish, Czech listopad, Belarusian ‘listapad’ (‘Лiстапад’) and Ukrainian “листопад” means November.
Thanks
Error occurred because It’s a SQL language that need to change we have faced as we have default language is us_english and as per your sql query has Croatian date included…
So need to
SET LANGUAGE Croatian and
then execute below query
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
it gives O/P without error.
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Listopad is month of october in Croatian language. So in regional settings of your client language may be Croatian, so its working at his end.
To run it under english language sql installation
Set Language Croatian;
Select convert(nvarchar….
Set language croatian
In your machine language was English and client machine language was polish that’s why its not work on your machine but work on client machine.you have to change your language English to polish.using
set language query.
this is due to langagus used by them
they are using Croatian langauge of there system
in Croatian month is oct is listopad
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
by this you can check your query
its working fine
SET LANGUAGE ‘Czech’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Your language setting is something other than Croatian, so SQL doesn’t understand how to decipher “listopad” – October in Croatian.
To execute the query successfully, use the SET LANGUAGE statement as follows:
SET LANGUAGE Croatian
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
This will change the language setting for the current session.
You can verify the server language setting using DBCC USEROPTIONS.
If you want to change the server default, you can use sp_configure or do it via SSMS.