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.
This puzzle is very simple – first, execute following script.
SELECT GETDATE()
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.
SELECT LEFT(GETDATE(),11)
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?
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)
168 Comments. Leave new
Looking at the LEFT function in BOL, it says LEFT can accept any datatype which can be converted to varchar or nvarchar. So there is a conversion done, under the covers. Easier to understand if you do the conversion step yourself:
select left(cast(getdate() as varchar (50)),11)
Fun puzzle and I learned something! Please post more.
As Left Implicitly convert the expression passed to varchar OR nvarchar and in this case LEFT convert the GETDATE() to VARCHAR and when you convert GETDATE() to VARCHAR it results in ‘MMM dd yyyy HH:mm tt’ format and so the result of SELECT LEFT(GETDATE(),11) is like “Resultset: Sep 19 2016”
LEFT returns varchar/nvarchar. It implicitly converts the datetime and uses the default style
same as CAST(GETDATE() AS nvarchar(11)) or CONVERT(nvarchar(11), GETDATE())
If you assign it to a datetime variable it will convert it to datetime
DECLARE @Date DATETIME = LEFT(GETDATE(),11)
SELECT @Date
Date format changed from yyyy-mm-dd to mmm dd yyyy because of the automatic conversion GETDATE() to string. To preserve default date format one should do SELECT CONVERT(date,LEFT(GETDATE(),11))
Here’s why LEFT(GETDATE(), 11) returns the result in “MON d yyyy” format. LEFT function performs an implicit CONVERT of the source object – GETDATE(), to VARCHAR, using the default style for converting datetime values of 0. Style 0 is the default, which equates to 100 – “mon dd yyyy hh:miAM (or PM)”. If you issue CONVERT(varchar, getdate(), 0), CONVERT(varchar, getdate(), 100), or CONVERT(varchar, getdate()), you will get the same result in the format specified above. The implicit conversion when using LEFT with offset 11 is the same as issuing the following statement: LEFT(CONVERT(varchar, getdate(), 100), 11), or, for brevity LEFT(CONVERT(varchar, getdate()), 11). Using just LEFT function performs the implicit conversion described above.
The LEFT function converts the date as STRING. The similar statement can be select cast(getdate() as nvarchar)
which returns “Sep 22 2016 1:22PM”. The LEFT function simply returns the first 11 characters from it.
because at use LEFT function in GETDATE, SQL convert DATE to VARCHAR and then to DATE again, and applies the default format from DATE
LEFT() function expects 2 Input parameter as given below.
LEFT(Character_expression, Integer_expression).
The output of the getdate() is implicitly converted to VARCHAR and then 11 Characters of it is extracted. Thats why the result is converted in the way as shown above.
The output of 2nd Query and 3rd query below will show the result above
–1st Query
SELECT GETDATE()
—2016-09-22 11:05:12.187
–2nd Query
SELECT LEFT(GETDATE(), 11)
–Sep 22 2016
–3rd Query
SELECT CONVERT(VARCHAR(11), GETDATE())
–Sep 22 2016
Thanks,
Hi Dave,
what heppens is an implicit conversion to string like
select CAST(getdate() as varchar)
=> Sep 22 2016 3:58PM
Here is what I found on Google. Hopefully it is the correct answer. :-)
When you do LEFT(GETDATE(), then the database needs to do an implicit conversion from datetime to some string value. For this, it uses its internationalization settings. What you are seeing is based on these settings.
I think the LEFT forces an implicit conversion of the datetime to a string, and that’s different from the way SSMS outputs raw datetime values in result sets. The implicit conversion is also dependent on the SET LANGUAGE setting.
Left is a string function and it converted date in string first before giving the output which is the reason why yyyy-mm-dd converted into mmm dd yyyy. If you try casting a date into varchar/string then also it will convert the date in similar format..
Hope that suffice your puzzle
Since I have nothing constructive to add or an answer that makes sense, I’ll say that it does that because Microsoft planned it that way so that Access would take over as the prominent database engine.
No?
Damn….
Hello,
When are the comments with answers going to be published?
Thank you
Ananthram
Very soon!
Still waiting for the comments…:)
Monday!
left requires string, so there is implicit conversion from date time to string, Convert to string without specifying a style result also in mdy format
Hi,
Using this LEFT(GETDATE()) do some implicit conversion from DATETIME to string value. Hence this result.
Better is to use CONVERT(CHAR(11),GETDATE()).
Thanks
for string functions if the input is not string it will be implicitly converted to the text data type, so it showed the 11 characters from left side.
Left is a string function. It implicitly converts the getdate() to Varchar datatype when using it.
Thanks!
Optimizer is auto converting to varchar because the LEFT command requires varchar parameter. Looks like left(convert(varchar(255),getdate(),101),11). Although, I’m not certain what number the optimizer uses for varchar. 255 is just a guess.
I am just guessing here. Is it because LEFT() is a string function? So LEFT() function will turn the date to string first. You get the same as 2nd format when you use CONVERT(NVARCHAR, GETDATE()). Also if the 1st format in your example is how the system stores it (This I am totally not sure…looking forward to your answer!), you should only be using LEFT(date, 10) instead of LEFT(date, 11)