The best way to build a query

Question

Entrance:

Month, Year, GraceMonth. All are in Integer.

What do we need to do?

First you need to create a date from the month, year and day (you need to get it from the current date) and then add GraceMonth to it. We will get a new Date, obviously after adding GraceMonth. Next, with the constructed date, we need to compare this with the current date.

What have you tried?

eg. (I show in parts)

 DECLARE @Month INT = 11 DECLARE @YEAR INT = 2012 DECLARE @Graceperiod INT = 2 SELECT [Construct Initial Date] = DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1) --construct initial date ,[Add Grace period] =DATEADD(mm,@Graceperiod,DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1)) --add grace month ,[DateDiff] = DATEDIFF ( DAY, DATEADD(mm, (@YEAR - 1900) * 12 + (@Month + @Graceperiod) - 1 , DAY(GETDATE()) - 1), GETDATE() ) -- datediff 

Result

 Construct Initial Date Add Grace period DateDiff 2012-11-14 00:00:00.000 2013-01-14 00:00:00.000 -122 

If your answer is correct, then what are you looking for?

Is there any other good approach besides this? The more succinctly without Casting, the better. And please provide an explanation if it involves some complex part (for example, some kind of complicated mathematical calculation).

Thanks in advance.

Answer 1

 DECLARE @Month INT = 11 DECLARE @YEAR INT = 2012 DECLARE @Graceperiod INT = 2 SELECT [Construct Date] = DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1) --construct date ,[Add Grace period] =DATEADD(mm,@Graceperiod,DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1)) --add grace month ,[DateDiff] = DATEDIFF( DAY, DATEADD(mm,@Graceperiod,DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1)), GETDATE() ) -- datediff 

Answer 2

try the following:

 DECLARE @Month INT = 11 DECLARE @YEAR INT = 2012 DECLARE @Graceperiod INT = 2 SELECT DATEADD(mm,(@month-month(getdate())),DATEADD(year,@year-YEAR(getdate()),getdate())) as InitialDate, DATEADD(mm,@Graceperiod,DATEADD(mm,(@month-month(getdate())),DATEADD(year,@year-YEAR(getdate()),getdate()))) as GraceDate, DATEDIFF(day,DATEADD(mm,@Graceperiod,DATEADD(mm,(@month-month(getdate())),DATEADD(year,@year-YEAR(getdate()),getdate()))),GETDATE()) as DateDiffs 

Answer 3

try the following:

This may not give any performance improvement, I think, but it slightly reduces your code.

 DECLARE @Month INT = 11 DECLARE @YEAR INT = 2012 DECLARE @Graceperiod INT = 2 ;with cte as (select DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 ,0) as begining_month) select DATEADD(dd,DAY(GETDATE()) - 1,begining_month) as [Construct Initial Date], DATEADD(mm,@Graceperiod,DATEADD(dd,DAY(GETDATE()) - 1,begining_month)) as [Add Grace period] , DATEDIFF(DAY,DATEADD(mm,@Graceperiod,DATEADD(dd,DAY(GETDATE()) - 1,begining_month)), GETDATE()) as [DateDiff] from cte 

Answer 4

You did the same calculation three times, try to save this

 DECLARE @Month INT = 11 DECLARE @YEAR INT = 2012 DECLARE @Graceperiod INT = 2 -- This is that calculation DECLARE @ConstructInitialDate DATETIME = DATEADD(mm, (@YEAR - 1900) * 12 + @Month - 1 , DAY(GETDATE()) - 1) SELECT [Construct Initial Date] = @ConstructInitialDate --construct initial date ,[Add Grace period] =DATEADD(mm, @Graceperiod, @ConstructInitialDate) --add grace month ,[DateDiff] = DATEDIFF ( DAY, DATEADD(mm,@Graceperiod, @ConstructInitialDate), GETDATE() )