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 Czech
set dateformat dmy
SELECT CONVERT (datetime, ’10 listopad 2015′, 113)
this will give proper answer for given statement
listopad is october month as I search and found from internet and its related to given language
normaly we set language to english US its gives error but by executing above gives date as result .
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Thank you Pinal for making us to an Extent that we are able to analyse and write such challenging code.
It is to do with Collation
That was given in language called “polish”, so it was working in the client sytem
SELECT CONVERT (DATETIME, ’10 October 2015′);
By placing the English month .
listopad is Polish for “November”. I suggest that you modify the default collation of the database that you are working with to:
88
plknc.250
Polish dictionary order, case-insensitive
SQL_Polish_Cp1250_CI_AS_KI_WI
This might require that you add this as a language to your server “language/regional options”, but I’m not 100% sure.
If we write above code by setting below language, then above code will work.
set language croatian
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
set language Polish
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
set language Czech
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
I have executed above queries on sql server 12.
listopad is the croatian name of month October.
Collation is the key, listopad is a Polish word for November. So we need to override the language for the script to use Polish language (use SP_HELPLANGUAGE to list all languages).
The clients system is configured to use lanuage id 12 – Czech . and ‘listopad’ means ‘november’ in czech
This doesnt seem to be practical to me without even making any changes to query
It should be culture problem. You need to set culture, not sure how we use it in SQL Server for particular query.
Dave:
Appreciated the challenge.
Here is my answer:
SELECT CONVERT(datetime,TRY_PARSE(’10 listopad 2015′ AS DATETIME USING ‘hr-HR’),113)
Thx,
Robert Major
Listopad is a Month(November) in Polish language. It also means October in Serbian. Change language of SQL server to Polish and we should get output as customer.
set language CROATIAN;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Please provide me answer to – SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
SET LANGUAGE Croatian;
SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);
Listopad is Polish for November, obviously part of your international work.
If the text had been ’10 November 2015′ it would have worked.
To change your login so it will work for you:
On the Object Explorer in SSMS
Go into the top-level Security tab,
Logins, and right-click the individual user account.
At the bottom of the default (General) page, set the Default Language to Polish
Log back in – done
listopad is Croatian for October. Need to change your locale for this to work properly (i.e. for SQL Server to recognize it.)
Hello Sir,
Thank you for sharing this, I have seen it first time.
Its amazing to learn this…
Following may be your answer: