SQL SERVER – Puzzle – Playing with Datetime with Customer Data

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.

SQL SERVER - Puzzle - Playing with Datetime with Customer Data puzzledatetime1-800x400

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:

SQL SERVER - Puzzle - Playing with Datetime with Customer Data sql-quiz-datetime-listopad-01-800x212

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:

  1. Why am I getting this error? How did it work on my client’s machine?
  2. What should I do to get this sorted on my machine?
  3. 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)

Quest

SQL DateTime, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Puzzle – Solve Splitting Strings Puzzle
Next Post
SQL SERVER – Puzzle – Change in Date Format with Function

Related Posts

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 .

    Reply
  • Ravindranath R Kanojiya
    September 16, 2016 3:09 pm

    SET LANGUAGE Croatian;
    SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);

    Reply
  • Ravindranath R Kanojiya
    September 16, 2016 3:17 pm

    Thank you Pinal for making us to an Extent that we are able to analyse and write such challenging code.

    Reply
  • It is to do with Collation

    Reply
  • That was given in language called “polish”, so it was working in the client sytem

    Reply
  • SELECT CONVERT (DATETIME, ’10 October 2015′);

    By placing the English month .

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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).

    Reply
  • The clients system is configured to use lanuage id 12 – Czech . and ‘listopad’ means ‘november’ in czech

    Reply
  • This doesnt seem to be practical to me without even making any changes to query

    Reply
  • It should be culture problem. You need to set culture, not sure how we use it in SQL Server for particular query.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • set language CROATIAN;

    SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);

    Reply
  • Please provide me answer to – SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);

    Reply
  • Ganaparthi Jagadeesh
    September 17, 2016 11:40 am

    SET LANGUAGE Croatian;
    SELECT CONVERT (DATETIME, ’10 listopad 2015′, 113);

    Reply
  • Jonathan Roberts
    September 17, 2016 5:23 pm

    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

    Reply
  • listopad is Croatian for October. Need to change your locale for this to work properly (i.e. for SQL Server to recognize it.)

    Reply
  • Shivendra Kumar Yadav
    September 18, 2016 8:10 am

    Hello Sir,

    Thank you for sharing this, I have seen it first time.

    Its amazing to learn this…

    Following may be your answer:

    SET LANGUAGE Polish
    SELECT CONVERT (DATETIME, '10 listopad 2015', 113);
    
    Reply

Leave a Reply