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
1: Client’s Server is having Croatian language, while your server has not.
2: By changing language to Croatian.
command is:
SET LANGUAGE Croatian
3: we can get existing culture language by
SELECT @@language
Language settings will have impact on datetime formats and system messages
SELECT * FROM SYS.syslanguages WHERE MONTHS LIKE ‘%listopad%’;
–Czech
–Polish
–Croatian
set language Czech;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE Polish;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
We just need to set Language environment for the session:
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Venkata R
We just need to set the language environment for the session
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
We will have to apply different collation, which client is using.
Or we will have to set the language for month column
Hi Dave, The Collation and Language of your customer DB is Croatian, thus your customer got the output for the conversion query. Herewith i have given the query for getting the output.
SET LANGUAGE Croatian ;
select CONVERT (DATETIME, ’10 listopad 2015′ , 113) ConversionOutput;
and the output will be,
Changed language setting to hrvatski.
ConversionOutput
———————–
2015-10-10 00:00:00.000
(1 row(s) affected)
Best Regards,
Jafer
I think it depends on “DefaultLanguage” server options which in this case is diffrent from ‘Croatian’ and ‘Polish’. In your setting probably this is ‘English’ where ‘listopad’ is unknown.
You can also set this option per session: SET LANGUAGE ‘Croatian’.
1,2. Because of the default language configured.
This code works :
SET language ‘Croatian’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
3. To change permanently : Go to Security -> Logins -> Right click on user and go to Properties -> Change default language to Croatian (disconnect and reconnect), the query works :
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
It works in client machine because in Croatian language Listopad means October, which is month.
1) Because of a different language settings on you machine compared to client’s machine
2) SET LANGUAGE Czech;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113)
3) Here are all the language settings possibilities
EXEC sp_helplanguage
Why am I getting this error? How did it work on my client’s machine? — Because by default language settings on your SQL server will be us_english, whereas on client machine it would be Croatian
What should I do to get this sorted on my machine? — To get this sorted execute “SET LANGUAGE Croatian”
What options and settings can you change and what all values can those take? — All the name defined in table select * from sys.syslanguages
Hi Dave,
Are you sure it’s Croatian? Since in Croatian “listopad” means October but in Polish it means November and that is the month which I can see on your screenshot. (2015-11-10 00:00:00.000)
Solution:
SET LANGUAGE hrvatski
SELECT CAST(’10 listopad 2015′ AS datetime) as [Croatian]
go
SET LANGUAGE polski
SELECT CAST(’10 listopad 2015′ AS datetime) as [Polish]
Kind regards,
Max
Hi Pinal,
Date stored in Polish Language, Need to set language for particular session, before execute below statements:
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, N’10 listopad 2015′, 113);
Thanks,
Fazal
It’s sql collation issue
The date is in Croatian language you convert it to solve the error
set language Croatian
GO
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
GO
listopad is Polish month of English November. The language settings need to be changed in order to run the query successfully.
SET LANGUAGE Croatian ;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);