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
Hi,
The format is changed because when we use LEFT function, it first converts the date into text and then it takes first 11 characters. We will see the same effect if we use
SELECT CONVERT(NVARCHAR,GETDATE())When a datetime datatype value use in a string function is cast date into string with mmm dd yyyy hh:mm format.
after that left function take only 11 leftmost character.
GETDATE() is a built in function that return datetime value whereas LEFT is a built in function that only returns a nvarchar value.When you are using LEFT for GETDATE() function then the datetime value is implicitly converted to nvarchar by the DBMS engine
i think below query should work
SELECT CONVERT(varchar(10), GETDATE(), 126)
LEFT function accept Nvarchar value as first argument, and sql convert datetime into nvarchar in mmm dd yyyy Fromat it self
So far amazing response… keep the answers coming!
LEFT() function requires character strings and count of characters to return starting from 1st position. Since, GETDATE() returns datetime value, entire datetime value returned by getdate() is implicitly converted to varchar (something like CAST(GETDATE() AS VARCHAR)) character string like mon dd yyyy hh:miAM (or PM). After this, applying LEFT(, 11) function, we get string in format mon dd yyyy.
Hi Pinal, the left function returns a string and so it does a explicit conversion and returns the default SQL format for datetime and as we specify the length so it prints only the first 11 chars.
If we use the
Select convert(varchar,Getdate()) , we’ll get the same value with time.
Question: Why did Why did displayed date format changed from yyyy-mm-dd to mmm dd yyyy?
Answer: Left T-SQL function parameters are expression as varchar and count as in, so it cast datetime in varchar and default style of datetime cast function is “mon dd yyyy hh:mmAM (or PM)” that’s why it gives result as first 11 chars of datetime after cast in varchar.
Example:
SELECT GETDATE()
SELECT CAST(GETDATE() AS VARCHAR)
SELECT CONVERT(VARCHAR,GETDATE(),0)
SELECT LEFT(GETDATE(),11)
LEFT has first parameter character_expression. So when you wrote LEFT(GETDATE(),11) sql server do implicit convert to nvarchar.
LEFT(GETDATE(),11) is equals LEFT(convert(nvarchar,GETDATE()),11)
But convert have default third parameter 100 for datetime type
I think left() is a string function so while doing left() the getdate() is casted from datetime to string. so when we cast date into varchar we get the mmm-dd-yyyy format. that is why the change.
left returns characters so it will convert this to the default string equivalent
That said, left(sysdatetime(),11) works
we use left and right functions for strings only but when we use left(getdate(),11) it select 2016-09-15 but it is numeric so it won’t satisfy condition for above it converts to mmm-dd-yyy as charcters
In SQL Server the function LEFT has to return a string value and hence when we do a LEFT on GETDATE() it has to be converted to some string format. So when we execute
Select Left(getdate(), 11)
the result is getdate() converted into a string (with its default conversion) or in other words it works similar to
Select Convert(VARCHAR(11), GETDATE())
and hence we get Sep 21 2016
Thanks
Nirav
LEFT is a string function, and therefore an implicit conversion is applied against the GETDATE which is of datetime which provides the result this way
It is similar to
Select cast(GETDATE() AS VARCHAR(24))
LEFT String function convert date to string of length 11
Select LEFT(GETDATE(),11)
Because the getdate() is first converted to varchar?
select cast(getdate() as varchar) gives me Sep 21 2016 11:07AM.
I think this is because the LEFT function does an implicit conversion of the character_expression (getdate()) in our case) to a varchar or nvarchar data type.
A explicit conversion to a varchar data type gives the same result:
select cast(getdate()as varchar(11))
Sep 21 2016
select left(getdate(),11)
Sep 21 2016
Best regards
Thierry
it is the Short date format of the system
from msdn getdate() “Returns the current database system timestamp as a datetime value ”
but LEFT “Returns the left part of a character string”, mean it first converts getdate() to ‘Sep 21 2016 9:29AM’ default format, and then takes left 11 chars.