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
LEFT takes in itself (Expression, Count), so most probably if it isn;t a binary input than it will convert it into a String.
GETDATE() returns a DATETIME datatype. When using the LEFT-function there is an implicit CAST to VARCHAR, which acts like the CONVERT-function with style 0 (which outputs: mon dd yyyy hh:miAM (or PM)). So taking the 11 leftmost characters leaves “mon dd yyyy”.
I am not sure if i am giving the right answer or not , but what I am getting is when we are using left function datatype is changing ( There may be some implicit conversion in DB itself ) .When we are printing the result either it is taking data as per the implisit conversion or it is first seleting the char and then considering the numeric part .
This must be implicit conversion as on executing below command I am getting char in first place and in last place.
SELECT left(GETDATE(),20)
Sep 21 2016 2:24PM
Why did displayed date format changed from yyyy-mm-dd to mmm dd yyyy?
The LEFT function returns the left part of a string, from a given number of characters. But the case is, GETDATE() function will return a DATETIME data type, and not string. That’s the trick. What happen is that the LEFT function will accept any character expression except text or ntext, that can be implicity converted to varchar or nvarchar. When the command is triggered, an implicit convertion from DATETIME to VARCHAR(11) using the default convertion style (e.g. mon dd yyyy hh:mi) happens behind the hoods, and only the first 11 charcters are shown.
LEFT is a string operation, so there must be a convert behind the scenes. As to why that format, maybe it is the string friendly format.
Because of implicit casting. You are using a function that requires a string argument, but instead you are passing a datetime. The function is doing implicit casting of a datetime to a string in order to satisfy its requirements, but in doing so the system is using the standard string representation of a datetime. A much easier way to drop the time piece of a datetime would be to:
CAST(GETDATE() AS DATE)
No time, no unexpected formatting.
LEFT is funcation which takes two parameter first is of type nvarchar and second int but in case of GETDATE()
it returns DateTime so it is first converting into nvarchar with the help of Convert function and the default
format for the convert function is ‘Sep 21 2016 7:15PM’ so finally ‘Sep 21 2016 7:15PM’ is passing to LEFT funcation
so we are getting o/p like ‘Sep 21 2016’
Code is As Follows:-
declare @today nvarchar(20);
select @today = CONVERT(nvarchar,GETDATE())
select @today
select LEFT(@today,11)
I never realized that would work without a convert – good share. Reason being I believe is implicit conversion to string type when passed in the the LEFT() string function resulting in default datetime string format. (0 / 100) – mon dd yyyy hh:miAM (or PM). Of course I believe the format is susceptible to region / language settings (I liked your previous post about language/region issues).
I’d probably never rely on that and still do the conversion.
Because by default conversion to varchar of a date uses MDY format. The default in SQL server for language is us_anglish and for dateformat is mdy. You can check the defaults with:
dbcc useroptions
Set Option Value
————————
…
language us_english
dateformat mdy
…
So, if you run this:
SELECT select cast(getdate() as varchar(30)), left(getdate(),11)
The first value is casted with default format while the second value first the getdate() result is implicitly casted to varchar due to LEFT function apply, which then only takes the first 11 characters out of it.
Forgot to put the results for select:
select cast(getdate() as varchar(30)), left(getdate(),11)
—————————— ———–
Sep 21 2016 10:24AM Sep 21 2016
GETDATE() uses Windows settings but LEFT() dos an implicit conversion from DATETIME to CHAR and the conversion is based on database settings and uses internationalization for the conversion.
It gets converted to Varchar
sql server converts dates to varchar to type 0 by default like this
convert(varchar,getdate() ,0)
that results to ‘Sep 21 2016 11:36AM’
then taking left 11 we get this:
”Sep 21 2016′
Even after manipulation as text, SQL Server still recognized the remaining characters as a date and is displaying that date in the default format set at the local level.
CONVERT ( DATE , GETDATE() )
Cast or convert can be used to convert a datetime field to a date field, thus eliminating thetime portion.
off the top of me head, “SELECT LEFT(GETDATE(), 11) would convert the result to a string. The differences in the formats would reflect the default formatting for GETDATE() is DATETIME, which renders the date portion as yyyy-mm-dd, while the default format when converting from a varchar (or nvarchar, or char(11)) back to a DATE type would be ddd mm yyyy.
It is because the LEFT function requires a char value, so the GETDATE() function is being automatically converted to char before the LEFT function is carried out, which writes the date out as seen in the query result.
When we use GETDATE(), it implicit convert it to varchar something like “sep 21 2016 10:38PM” and we are taking LEFT(GETDATE(),11) in this quiz. So it’s taking “sep 21 2016”.
I would suppose this is due to SQL Server converting from datetime type to varchar() or char() type.
You get the same change in display if you run select convert(varchar(50),getdate()).
Hope I am on the right track!
Great puzzle btw!