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
Not an answer, but is there a reason you don’t use (or prefer) the format function:
SELECT Format(GetDate(),’yyyy-MM-dd’)
The function LEFT(expression,no of chars) first convert expression in nvarchar then give LEFT part.So if we Convert GetDate() in nvarchar it gives result in mmm dd yyyy (Sep 21 2016 11:24PM) Format.
Because as soon as you apply LEFT operator, getdate(), which by default is a number, gets converted to string in format ‘mon dd yyyy’ and that is the default style for SQL. It is documented in the CONVERT function as style 0 or 100.
first it is converting to varchar than taking first 11 select left ( convert(varchar(50),GETDATE(),109),11)
The Behaviour of the Left Operator, it will convert the data type to VarChar(non-Unicode character) or nVarChar (Unicode character). Thats why the result coming in Varchar(with default conversion ).
When We put LEFT , it will get convert into the String so to avoid this We need to select below statement
SELECT LEFT(convert(varchar, getdate(), 120) ,11)
Hi Pinal
SELECT GETDATE() : This Statement returns the Current Date In date format in sql server.
So output is like ‘2016-09-22 11:41:39.663’
SELECT LEFT(GETDATE(),11) : This Statement will return 11 characters from the left and Interesting fact is you are using LEFT() Function
So it will return the date by converting to string format.
Like if you will execute select Convert(varchar(50), GETDATE()) then you will get
” Sep 22 2016 11:52AM ” As Output result.
Ex: Select left(200,2)+left(300,2) It will return 2030
Hello sir,
Its default style format (i.e. 0 or 100).
See below code..
SELECT GETDATE() , LEFT(GETDATE(),11) , CONVERT(VARCHAR(10),GETDATE(),121) , CONVERT(VARCHAR(11),GETDATE(),0) --Default One , CONVERT(VARCHAR(11),GETDATE(),100) --Default OneHi Sir,
Some of the string functions in SQL implicitly converts the expression to string type such as LEFT, RIGHT etc.
Here the result of GETDATE() will implicitly be converted to string which will be in mon dd yyyy hh:mm AM/PM format.
This is the reason we are getting the result with the changed format.
When we do ‘SELECT GETDATE()’ then it shows the data with ISO Standard. At the time of ‘SELECT LEFT(GETDATE(), 10)’ it implicitly convert the datetime value to string. That follows the server’s internationalisation settings. This is the reason.
It is taking system date settings (‎September ‎22, ‎2016)
Hi Pinal,
When we do GETDATE(), SQL Server shows the results in the form of ISO/ANSI standards.The Date format changes when we use LEFT() because the SQL server tries implicit convert from date-time to some string value before applying the LEFT(). So rather we should on explicit conversion methods to achieve that.
We can use below script – SELECT LEFT(CONVERT(VARCHAR,GETDATE(),121),11)
because of left() functions take first parameter nvarchar value,we pass the getdate() value,i think sql server convert as nvachar value,we provided that select cast(getdate() as nvarchar) query.
It is due to:
– 0 or 100 (1,2) Default for datetime and smalldatetime mon dd yyyy hh:miAM (or PM) – the Left() function requires a string and the date is converted into a string, which uses the default of 0 or 100… see this example below:
select convert(varchar,getdate()) – produces: Sep 22 2016 5:02AM
I didn’t actually know this before.. but upon you showing he behavior, I learnt something by solving the puzzle at least from my understanding.
AFAIK, It is because datetime already set as fixed format. When it is assigned to a new string datatype (Char, Varchar), it will no longer maintain its format and will go to default standard format. Thats why every conversion using string must define its output format (such as 120 or 112).
GetDate() on its own is displayed as a ‘native’ date format.
Performing a left(GetDate(),11), first converts the GetDate() to a varchar representation of the date and then takes the left 11 chars.
This can be shown by
SELECT CONVERT(VARCHAR, GetDate())
which will output the date as Mmm DD YYYY HH:MM…
It just so happens that, by luck, the first 11 chars in this format also map to the base date without the time.
Hello Sir,
First of all I want to thank you for all the posts you shared with us, whenever I get time I used to go through your blogs only to know about the small small concepts which I never think before.
As per my view the Getdate() function returns a datetime value, on the other hand when we are using left using Getdate function (LEFT(Getdate(),11)), in that case the database will do the implicit conversion to string datatype.
Please let me know if I am correct…Thanks a lot once again…:)
— Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
LEFT ( character_expression , integer_expression )
left need a character_expression, this mean implicit conversion
see what happens
SELECT GETDATE()
2016-09-22 12:22:48.097
SELECT CAST(GETDATE() AS NVARCHAR(max))
Sep 22 2016 1:01PM
SELECT LEFT(GETDATE(),11)
Sep 22 2016
Well, The LEFT function requires the first argument of string type. So when we use the GETDATE() expression as a first parameter, it automatically gets converted into string format using the follwowing convert function.
SELECT CONVERT(Varchar(20),GETDATE(),100)
It will convert the system date to following format “mon dd yyyy hh:miAM (or PM)”.
After that, LEFT function will select the first 11 cahrecters which happens to be date part only excluding time component.
Try running this queries, you will get a clear understanding.
Select GETDATE()
SELECT CONVERT(Varchar(20),GETDATE(),100)
SELECT LEFT(GETDATE(),11) , LEFT(CONVERT(Varchar(20),GETDATE(),100),11)