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



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

Perhaps a bit tidier:

SELECT YEAR(@dateb)*100 + MONTH(@dateb);
  • 1
    I agree with this wholeheartedly. You have a date, which has integer subfields; you want an integer. Why on earth do any string processing? Sep 10 '19 at 21:05

Could this do the trick for you?

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

Ah sorry, I forgot, you'll need a substring too. So it's:

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

Another method:

DECLARE @PeriodToCalculate_DATE [DATE] ='2016-02-29'

   CAST(DATEPART(YYYY,@PeriodToCalculate_DATE) AS [CHAR](4))
       + RIGHT('0' + CAST(DATEPART(M,@PeriodToCalculate_DATE) AS [VARCHAR](2)),2)  
       + RIGHT('0' + CAST(DATEPART(D,@PeriodToCalculate_DATE) AS [VARCHAR](2)),2);

Gives: 20160229

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.