It is a common requirements in data warehouses to convert today's date to the following format YYYYMMDD using T-SQL which also ISO format.
For instance we want the following date 24 February 2011 to display as 20110224. In most cases we will change datetime data type so no extra casting is required.
TSQL provides several ways to do that however the most efficient way to get YYYYMMD format is to use Convert function.
Convert function takes 3 arguments:
Target data type: Although the end result should be integer, you need to use NVARCHAR(8) to avoid converting date to integer.
You might be aware that computers don't store date as date but as number and SQL Server uses 1900-01-01 as 0 so 24 Feb 2011 would be 40596
(days from 1st Jan 1900)
Expression (=Date): The date field or variable or function. You might have to convert your variable or field to datetime using Cast(@variable as datetime)
Date Style: 112 is the ISO style which returns the result in YYYYMMDD format.
Below are T-SQL examples:
--Using today's date
Select CONVERT(nvarchar(8), getdate(),112) as [Date in YYYYMMDD Format]
--Using variable
DECLARE @Date as datetime
set @Date = '2010-02-23'
select convert(NVARCHAR(8),@Date,112) as [Date in YYYYMMDD Format]
Below is screenshot of the final result:
Hope that helps!
Katie & Emil
|
Share:
Comments Add Comment
Name:
Md. Salman
Comment:
when you want an integer, why avoid converting it to one ?
thanks for the info.
appreciate it.
Date Posted:
07/05/2011 9:30:30 PM
Name:
Emil
Comment:
SQL Server stores dates as integers and formats them as date/time. Unfortunately when you try to use INT in convert function it seems to change date to integer (14th of May 2011 in stored as 40675 and then "forgets" that it was date so display result as 40675 (it cannot apply format on non-date value) instead of 20110514.
Take care
Emil
Date Posted:
14/05/2011 7:06:29 AM
Name:
chandu
Comment:
We can use replace function also to bring the date into this format.
Date Posted:
29/08/2012 11:32:27 AM
Name:
Emil
Comment:
Hi Chandu
Replace will work only with string and most date fiels are date or datetime data type.
Regards
Emil
Date Posted:
29/08/2012 6:35:07 PM
Share your thoughts, questions and suggest improvements:
Add Comment
Consulting & Training Company
