SQL SERVER – Puzzle – Change in Date Format with Function

Last week we had amazing time with an interesting puzzle about Datetime. You can see the puzzle over SQL SERVER – Puzzle – Playing with Datetime with Customer Data. The puzzle was extremely well received and lots of people asked me to bring another puzzle which can help us learn something new. Looking at the request of everyone, here is another very simple puzzle with Date Format.

SQL SERVER - Puzzle - Change in Date Format with Function puzzledatetime2-800x400

This puzzle is very simple – first, execute following script.


When you execute above script, you will notice that it will display the current date in the format as yyyy-mm-dd, along with the date it is also displaying time values as well.

Resultset: 2016-09-19 12:32:58.737

Now let us assume that we do not want the time part in our display, we only want to display date part of the date time value. There are many different methods to get only date part, here is a relevant blog post: SQL SERVER – Retrieve – Select Only Date Part From DateTime – Best Practice.

Now let us assume that we have not read the blog post about how to select only date part from datetime. Now from visual inspection, it is very clear that we only want to retrieve left 11 characters from the datetime value. In SQL Server we have function LEFT(string,n) when we apply it over any string, it returns us n characters starting from the left. Let us apply the LEFT () function over our datetime function and retrieve only date part from it.


When you execute above script, it does give us only date part, but the value of the data part is changed from yyyy-mm-dd to mmm dd yyyy.

Resultset: Sep 19 2016

The Puzzling question is –

Why did displayed date format changed from yyyy-mm-dd to mmm dd yyyy?

SQL SERVER - Puzzle - Change in Date Format with Function displaydateformat

Well, just leave a comment in the blog post and after 2 days I will publish all the comments. If you know the answer of this puzzle, I suggest you ask this question to your friends and see if they know the answer of the same.

Reference: Pinal Dave (https://blog.sqlauthority.com)

SQL DateTime, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Puzzle – Playing with Datetime with Customer Data
Next Post
SQL SERVER Puzzle – Conversion with Date Data Types

Related Posts

168 Comments. Leave new

  • The data type conversion processed during select. Check the code below

  • Karthick Annamalai
    November 25, 2016 1:03 pm


    Below is my comments.

    LEFT ( character_expression , integer_expression )


    _character_expression can be a constant, variable, or column.
    _character_expression can be of any data type, that can be implicitly converted to varchar or nvarchar.
    So, the below query returns,
    select GETDATE()

    2016-11-25 12:50:27.767
    If we convert this into varchar,
    select cast(GETDATE() as varchar)

    Nov 25 2016 12:57PM
    The above cast conversion take implicitly in left () function.
    After this conversion, it select number of character from this results as below,
    select left(getdate(),11)

    Nov 25 2016

    select left(getdate(),20)

    Nov 25 2016 12:57PM

  • Expression any data type of expression. If it isn’t a binary type then it will convert to a string data type.

  • Esther Xaviour
    May 19, 2018 12:05 am

    Left() is a string function. So the date is converted to string data type


Leave a Reply