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
Set language ;)
Maybe his Windows language settings is polish (polish “listopad” means november , and croatian “listopad” means October )
–Set the language to Croatian as below
SET LANGUAGE polski
–then below query will work as expected.
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Thanks,
Girish
Polish months )
Listopad is different language word that meaning is “November” month. we have to change our machine default language for running above for datetime because in English there is no any word like listopad. that’s why its give datetime error
I’m guessing that the server on which the SQL is installed needs a language pack.
It’s a Interesting Puzzle the Answer is following its working on language in sql
SET LANGUAGE Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
This is related to the language setting in SQL. ‘listopad’ is October in Croatian language so the following will work..
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
The result will be 2015-10-10
Hi,
You was getting this error because your machines language was different and “listopad” was not in months names.
You need to change the language to Czech to execute this code.
SET LANGUAGE Czech;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
You can see the list of all languages and their respective months name in view sys.syslanguages.
Set language CROATIAN
SELECT CONVERT(datetime ,’10 listopad 2015′,113)
SET LANGUGE CROATIAN
GO
–Different language setting
select * from sys.syslanguages
WHERE months like ‘%listopad%’
select * from sys.syslanguages
Set Language ‘čeština’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Set Language ‘polski’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
–Different language setting
select * from sys.syslanguages
WHERE months like ‘%listopad%’
select * from sys.syslanguages
Set Language ‘čeština’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Set Language ‘polski’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Why am I getting this error? How did it work on my client’s machine?
Answer – By default your database has different language setting
What should I do to get this sorted on my machine?
Answer – Change language setting
What options and settings can you change and what all values can those take?
Options are
Set Language ‘čeština’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Set Language ‘polski’
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
–PawanKumar Khowal
Set Language ‘hrvatski’
go
select convert (datetime,’10 listopad 2015′,113)
go
Set Language ‘us_english’
go
SELECT @@language
Because name of month is on Croatian, you should SET LANGUAGE Croatian in session in which you want doing Convert
1. Why am I getting this error? How did it work on my client’s machine?
The error is due to Regional Settings does not match with the current parameter. After changing the Country or Region Languages to ‘Crotian’ it will overcome the error.
2. What should I do to get this sorted on my machine?
Add the language ‘Croatian’ in the Languages section of Region & Language setting.
3. What options and settings can you change and what all values can those take?
Only Add the language ‘Croatian’ and it will work.
1. I think you have another language settings on your server. Check select @@language please. I think Client machine have settings with Croatian language.
2. Set language please before query. SET LANGUAGE Croatian;
3. You can look at sys.syslanguages. SET LANGUAGE change First day of the week, month and weekday names,
Date Literal – 10 listopad 2015
Possible meaning – 10th Nov 2015
Language – Croatian (The Croatian months used with the Gregorian calendar)
Hi Pinal,
I think it is the problem due to non-English language (‘listopad’). it is working on client machine because may client has non English language as default language in general settings. or we can run this query as follow
SET LANGUAGE polish;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
When I have rewritten this query as SELECT CONVERT (DATETIME, ’10 November 2015′, 113); then it is working good on my machine.
Thanks,
Prakash Patel
Listopad means October in Croatia