Datepart of month in store procedure

Question

I declared @workingdatekey as below.

I need to define @newdatekey by subtracting one month from @workingdatekey which showld be in yyyymmdd format.

Both @workingdatekey and @newdatekey are in yyyymmdd format.

  Declare @workingdatekey int
  set @workingdatekey =  CONVERT(int,CONVERT(varchar(20),GETDATE()-1,112))

Answer 1

DECLARE @workingdatekey int
SET @workingdatekey =  CONVERT(VARCHAR(8),DATEADD(MONTH, -1,GETDATE()),112)

SELECT @workingdatekey

RESULT: 20140205

OR

DECLARE @workingdatekey int
SET @workingdatekey =  CAST(CONVERT(VARCHAR(8),DATEADD(MONTH, -1,GETDATE()),112) AS INT)

SELECT @workingdatekey

RESULT: 20140205

Either will get you the same result.

What you were trying to do GETDATE()-1 subtracts a day from the current datetime. You need to use DATEADD() function to add or subtract intervals to a datetime value.