Convert date yyyy-mm-dd to integer YYYYMM

Question

How can I convert @dateb:

SET @dateb = dateadd(month, datediff(month, 0, getdate()) - 3, 0)

that returns 2014-04-04 as date to an integer of 201404

Thanks

Answer 1

On version 2012 or higher you can use the format function to get just year and month, then cast it as an int.

On versions prior to 2012 you can do the formatting with the convert function, then cast as int.

declare @dateb datetime
set @dateb = getdate()

select cast(format(@dateb,'yyyyMM') as int) --2012 or higher
select cast(convert(varchar(6),@dateb,112) as int) -- all versions

Answer 2

Perhaps a bit tidier:

SELECT YEAR(@dateb)*100 + MONTH(@dateb);

Answer 3

There is convert(varchar(8), ..., 112) for this. A varchar with style 112 will default to varchar(30) if you do not set it to varchar(8) - and you could even set it to char(8) since the length is fixed.

See:

• Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so.

• Date and time styles - Microsoft Learn - CAST and CONVERT (Transact-SQL):

  

set @dateb = cast(convert(varchar(8), dateadd(month, datediff(month, 0, getdate()) - 3, 0), 112) as int)

Out:

20240101

To get the needed first 6 digits, you can take the substring() function:

select cast(substring(convert(varchar(8), dateadd(month, datediff(month, 0, getdate()) - 3, 0), 112), 0, 7) as int)

Out:

202401

Answer 4

Another method, though not the fastest nor easiest and just to show something more:

DECLARE @PeriodToCalculate_DATE [DATE] ='2016-02-29'
SELECT 
    CAST(DATEPART(YYYY,@PeriodToCalculate_DATE) AS CHAR)
        + RIGHT('0' + CAST(DATEPART(M,@PeriodToCalculate_DATE) AS VARCHAR),2)

        + RIGHT('0' + CAST(DATEPART(D,@PeriodToCalculate_DATE) AS VARCHAR),2);

Gives:

20160229

And if you need only YYYYMM, just drop the last line.

Gives the needed:

201602