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
The answer lies in the definition of LEFT function :-
LEFT ( character_expression , integer_expression )
character_expression
Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.
The important thing to notice in the above definition is that text or ntext will be implicitly converted to varchar. So if we simple do like below :-
select CAST(GETDATE() AS VARCHAR(MAX))
We get the format as mmm dd yyyy (Sep 21 2016 10:41AM) which by the way is the default format of converting datetime to varchar string.
Hence left function treats it as varchar string with the same default format of mmm dd yyyy.
Sir by default it will convert in text , and return the output of convert(varchar(11),getdate())
LEFT() function takes string parameter. Hence Date() internally converts as string date format
The Left keyword returns the output as varchar and takes the conversion code 100 (default value) for converting date time to varchar.
Hi Pinal
It is due to the fact that GetDate() function returns the value in datetime format where as LEFT() function works on teh character expression so implicitly it cast the DateTime value to the varchar which translates to the mmm dd yyyy format by default.
There is nothing strange.Function “Left” expects character exprssion. Function “GetDate” returns datetime, so an implicit conversion occures.
Similar result is using
SELECT
CAST(GETDATE() AS NVARCHAR(11))
Since Left returns varchar or nvarchar. just like
select cast(getdate() as varchar(11)) –will return sep 21 2016
so left converts the date into varchar then returns the date as sep 21 2016
The date format is changed because when you do a LEFT() SQL Server has to do an implicit conversion of the datetime to string. When converting to string it uses the locale and collation to determine how the date is presented
This is why the date is coming out in the format mmm dd yyyy because your internalization settings are based on the English and US Locale
Pinal Dave,
The reason the date comes out like Sep 21 2016 is because the left function is a string function, so when you use left you have converted a datetime to a string. You get the same answer if you use cast(getdate() as varchar(11)). If you only want the date you should be using cast(getdate() as date) .
Thx,
ROBERT MAJOR
It is converted to varchar so the format is changing
select left(getdate(),11)
select left(cast(getdate() as varchar(50)),11)
both the above queries will give same result
When we use left(getdate(),11) it internally converts date into varchar and takes 11 characters from the left
The LEFT function return nvarchar while datetime to nvarchar conversion does the format change
select CONVERT(nvarchar,getdate())
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
First entry in table
– 0 or 100 (1,2) Default for datetime and smalldatetime mon dd yyyy hh:miAM (or PM)
Cheers
Getdate() is a date function however LEFT is a string function. When you use LEFT with Getdate(), database tries to do implicit conversion using its internationalisation settings. This is why the format gets changed.
Pinal:
The answer has to do with the second parameter. I do enough work with dates that I created a blog post so that I can quickly choose the second parameter to get the date formatted like I need it.
The LEFT function internally converts its 1st argument(character_expression) to a Nvarchar one. If we want that to be displayed in a specific format we would need to convert GETDATE() to a suitable format and then use LEFT function.
Because LEFT implicitly converts the DATETIME data type to VARCHAR. And if you cast a DATETIME to VARCHAR, you get that string: SELECT CAST(GETDATE() AS VARCHAR()). You can get the exact same result with SELECT CAST(GETDATE() AS VARCHAR(11)).
Hi Dave,
Left function needs a string input. For this, it converts the output of getDate() function to string. And the default conversion format is “mon dd yyyy hh:mi(AM/PM)”. Hence left works on the result in that format and returns “Sep 19 2016”.
Bcoz , probably left() is a string related function. Probably the getdate() function is intelligent enuff to convert the returned value into string itself
THe datatype changes to string datatype from datetime datatype. So the value changes.