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]
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