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://darkslategrey-bat-805937.hostingersite.com)
168 Comments. Leave new
The left function results in an implicit conversion of the datetime value returned by GETDATE() to a character string using the 100 style code. Taking the left 11 characters gives you the date in mmm dd yyyy format.
FYI, I disagree with what you call a best practice in returning just the date portion of a date/time value.
Left() changes the data type from datetime to varchar() causing it to be considered a string and converting to mmm dd yyyy.
I would not use left to get just the date, I would use cast (getdate() as date) which will return mm-dd-yyyy
The LEFT function treats the parameter as a string or varchar.
My guess is because you’re using a string function on a function that normally returns a DATETIME data type, that there is an implicit cast/convert to VARCHAR before hand. (Generally I cast GETDATE() to DATE rather than using a string function to get the date in the same format without seeing the issue above.) The default format for casts/converts is “mon dd yyyy hh:miAM” so the implied cast is formatting it as such, and then the string function is being performed on it.
The integer_expression parameter for LEFT() counts a UTF-16 surrogate character as one character.
LEFT causes an implicit conversion to a string. What the default for such a conversion? If you look in Books Online under the key phrase of “CONVERT FUNCTION”, the “Date and Time Styles” chart shows that style type of “0” (same as “100”) is realized and the first 11 characters match what was returned in the example.
Rather than rely on the “DEFAULT” mark in Books Online, let’s go straight to the horse’s mouth and ask the code what it’s actual doing to try to confirm the documentation. This is a little bit difficult because, if you turn on the Actual Execution plan and run the following query…
SELECT LEFT(GETDATE(),11);
… you get nothing for an execution plan. It doesn’t even show up as a tab.
If, for the same query, you click on “Display Estimated Execution Plan”, you finally get something that says it’s a “SELECT WITHOUT QUERY” with no hints about what it’s actually doing either in “properties” or, if you view the estimated execution plan as XML, is equally devoid of any hints and only confirms the previous finding with…
So, let’s trick it into spilling it’s guts by making it think that it’s a “Select WITH a query”. Let’s use a 1 row “Pseudo-Cursor” to trick it. With the Actual Execution Plan turned on, run the following code, which returns the same result as the previously posted code but now has an actual execution plan associated with it.
SELECT LEFT(GETDATE(),11)
FROM (VALUES (0))v(N)
;
In the resulting execution plan, you find a “Constant Scan” block and there lies the proof. If you look at the properties for that block, it returns the following, which precisely and exactly identifies what the code is actually doing. Here’s the snippet from properties…
(Scalar Operator(substring(CONVERT_IMPLICIT(varchar(40),getdate(),0),(1),(11))))
To summarize…
1. SQL Server realizes that LEFT will require a conversion from DATETIME to a character based value.
2. It adds CONVERT_IMPLICIT, which follows the same rules and format as CONVERT, in this case.
3. It converts the date time using the conversion, which clearly shows that a DATE and Time STYLE of “0” is being used.
4. As a bit of a sidebar, it also changes LEFT to a SUBSTRING starting a character 1 for a length of 11 characters.
If we had to write it out in code ourselves, we’d end up with the following (I’ve added spaces to make ownership of the operands a bit easier to discern)…
SELECT SUBSTRING( CONVERT(VARCHAR(40),GETDATE(),0) ,(1),(11))
The bottom line is that LEFT causes an implicit CONVERT and the default STYLE for date and time conversions is, in fact, STYLE = “0” but now we don’t have to take anyone’s word for it even if it’s official documentation. We can actually see what it’s doing.
–Jeff Moden
Hi Pinal,
The basic thing is the function LEFT is of type string and GETDATE() is of type date. When process do call LEFT() function to get part of GETDATE() value without any cast/convert, then SQL Server internally typecast the GETDATE() value to string. So when it typecast the GETDATE() value to string, the returned value would be in the format “Mon DD YYYY HH:MIN AM”.
That is why the outer string function LEFT() returns the first 11 characters i.e., “Mon DD YYYY”.
In simple word the query SELECT LEFT(GETDATE(),11) would be internally translated as SELECT LEFT(CAST(GETDATE() as varchar),11)
Thanks,
Chandrakanth R
By using LEFT you implicitly convert getdate() expression to varchar(11) datatype. Default style for such conversion is 0, and the format for this style is mon dd yyyy hh:miAM (or PM) so it is applied. See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
I assume that both operations work directly on the DATETIME datatype. The conversion for visual display when selected to yyyy-mm-dd hh:nn:ss,mmm is just different from the implicit conversion to VARCHAR when the LEFT function is used .. am I near?
In 1st select statement date and time output is in default character set format as format is not mentioned in sql statement.
SELECT GETDATE()
—2016-09-19 12:32:58.737
–2nd statement is a request of date information in 11 character set format.
SELECT LEFT(GETDATE(), 11)
–Sep 19 2016
The left function implicitly converts to character data type and uses the default language settings to determine the default date display conversion. These can be changed in the instance configurations or using SET LANGUAGE. Alternatively, you can use SET DATEFORMAT to only effect the date display for the implicit conversion.
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms174398(v=sql.105)
IMHO, yes. There is a rather significant reason to NOT use the FORMAT function. It’s been proven to be 44 times slower than CONVERT.
I would think that applying a format, which is just displaying the date in an applied format rather than converting from one type of object to another would be faster, but you are correct sir.
I just did a quick check on a non indexed date field:
SELECT ServiceDateBegin FROM rpt_DMHClaim WITH (NOLOCK) — 749808 records in 7 sec
SELECT FORMAT(ServiceDateBegin,’MM/dd/yyyy’) AS SvcDate FROM rpt_DMHClaim WITH (NOLOCK) — 25sec
(re-ran first select to confirm it still takes 7 seconds)
SELECT CONVERT(VARCHAR(11),ServiceDateBegin,110) AS SvcDate FROM rpt_DMHClaim WITH (NOLOCK) –8sec
So it appears in my case CONVERT is three times faster than FORMAT.
Good to know! :)
Fair point.
@David,
First, thank you for the feedback. I very much appreciate it. Depending on how you actually tested and what the table you tested against looks like, and based on you finding it only 3 times slower, please consider the following.
Two things about your testing that may cause FORMAT to appear to be only 3 times slower. First, if you’re returning the data to the screen (especially that number of rows), remember that displaying the data is the “Great Equalizer” when it comes to code. Consider dumping the result to a “bit bucket” variable to take the display out of the picture.
Second, you don’t need to do the conversion to VARCHAR(11), which has some bit of extra overhead compared to CHAR(11). Further, if you’re using Style 110, it only returns 10 characters so the conversion should be to CHAR(10).
Third, you might also be running into the other “Great Equalizer” and that’s a Clustered Index scan. When testing the performance of a function, you need to test only the function and keep as much interference from unrelated disk and display functionality out of the picture.
Also, Style 101 is the equivalent of the “MM/DD/YYYY” format string that you have in your FORMAT example.
With all that in mind, please run the following code, which makes and does a test of only the functions without outside factors affecting the test. Details are in the comments in the code, as always.
______________________________________________________________
–===== If the test table already exists,
— drop it to make reruns in SSMS easier
IF OBJECT_ID(‘tempdb..#MyHead’,’U’) IS NOT NULL
DROP TABLE #MyHead
;
–===== Create a million random dates as the DATETIME
— datatype for the 2010 decade and store them in
— a Temp Table. This only takes a second.
SELECT TOP 1000000
SomeDate = DATEADD(dd,
ABS(CHECKSUM(NEWID()))
% DATEDIFF(dd,’2010′,’2020′),
‘2010’)
INTO #MyHead
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
GO
PRINT ‘–===== CONVERT mm/dd/yyyy (101) ==========’;
SET STATISTICS TIME,IO ON;
DECLARE @BitBucket CHAR(10);
SELECT @BitBucket = CONVERT(CHAR(10),SomeDate,101) –mm/dd/yyyy
FROM #MyHead;
SET STATISTICS TIME,IO OFF;
GO
PRINT ‘–===== FORMAT MM/dd/yyyy w/o Datatype Match ==========’
SET STATISTICS TIME,IO ON;
DECLARE @BitBucket CHAR(10);
SELECT @BitBucket = FORMAT(SomeDate,’MM/dd/yyyy’)
FROM #MyHead;
SET STATISTICS TIME,IO OFF;
GO
PRINT ‘–===== FORMAT MM/dd/yyyy w/ Datatype Match ==========’
SET STATISTICS TIME,IO ON;
DECLARE @BitBucket NCHAR(10);
SELECT @BitBucket = FORMAT(SomeDate,N’MM/dd/yyyy’)
FROM #MyHead;
SET STATISTICS TIME,IO OFF;
GO
______________________________________________________________
Here are the run results from one of my production boxes. Like I said, 44 times slower except it turns out to be 60 times slower, in this case.
(1000000 row(s) affected)
–===== CONVERT mm/dd/yyyy (101) ==========
Table ‘#MyHead_____________________________________________________________________________________________________________0000003108D1’. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 391 ms, elapsed time = 790 ms.
–===== FORMAT MM/dd/yyyy w/o Datatype Match ==========
Table ‘#MyHead_____________________________________________________________________________________________________________0000003108D1’. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 23671 ms, elapsed time = 48974 ms.
–===== FORMAT MM/dd/yyyy w/ Datatype Match ==========
Table ‘#MyHead_____________________________________________________________________________________________________________0000003108D1’. Scan count 1, logical reads 2101, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 24000 ms, elapsed time = 50889 ms.
p.s. If you use the DATE data type instead of DATETIME in the test table I create in the code above, all times are virtually cut in half because the DATE datatype only has 4 bytes instead of 8.
Here are the run results from that.
(1000000 row(s) affected)
–===== CONVERT mm/dd/yyyy (101) ==========
Table ‘#MyHead_____________________________________________________________________________________________________________00000031201D’. Scan count 1, logical reads 1484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 391 ms, elapsed time = 399 ms.
–===== FORMAT MM/dd/yyyy w/o Datatype Match ==========
Table ‘#MyHead_____________________________________________________________________________________________________________00000031201D’. Scan count 1, logical reads 1484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 23250 ms, elapsed time = 24150 ms.
–===== FORMAT MM/dd/yyyy w/ Datatype Match ==========
Table ‘#MyHead_____________________________________________________________________________________________________________00000031201D’. Scan count 1, logical reads 1484, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 23484 ms, elapsed time = 24677 ms.
Because left function has nvarchar perameter that’s why on call it is converting the datetime into nvarchar.
select GETUTCDATE()———–2016-09-27 10:29:35.630
select convert(nvarchar,GETUTCDATE())————-Sep 27 2016 10:29AM
select LEFT(getutcdate(),11)——————Sep 27 2016 (This is first 11 character of “Sep 27 2016 10:29AM”)
Please execute below query you will find the answer
select convert(varchar, getdate()) or select convert(nvarchar, getdate())
Answer is when we use Left with any datatype it treat as string and apply Left on that value.
Similarly if we execute below query it will show 123.4 because it first treat that value as numeric then directly convert that value to string format so after converting that string becomes 123.45 so it takes first 5 letters in it.
select left(000000123.45,5)
I was wrong, technically, because I should have had 100 instead of 101…
left(convert(varchar(255),getdate(),100),11)
GETDATE() returns a datetime value. When you do SELECT GETDATE(), then the application is getting a datetime value and figuring out how to display it. The application you are using is wisely choosing an ISO-standard format.
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.
The Left function automatically convertiert (cast) datetime in format (mon dd yyyy hh: miAm (or PM)) .
see
https://docs.microsoft.com/en-us/sql/t-sql/functions/left-transact-sql?view=sql-server-2017 and
https://docs.microsoft.com/de-de/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017