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:

Solarwinds

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)

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

Related Posts

187 Comments. Leave new

  • It may cause of creation language environment setup on client machine.

    Reply
  • Prashanth KUmar
    April 11, 2017 7:36 pm

    SET LANGUAGE Croatian;
    select convert(datetime,’10 listopad 2015′,113)

    Reply
  • NISHIKANT GOUTAM
    April 27, 2017 8:59 pm

    set language Polish

    select convert(datetime, ’10 listopad 2015′, 113)

    –This will also result as expected ,SET LANGUAGE Croatian; is best way to do it , but in Czech and Polish listopad means November – rather than October in Croatian – perhaps because the leaves stay on the trees longer in those countries.

    Reply
  • write string date with complete format ‘2017-05-19 00:00:00’ HH:MM:SS at least or with ‘.000’

    Reply

Leave a Reply

Menu