An interesting scenario with Dates Calculation

• uravindarreddy

  Ten Centuries

  Points: 1390

  More actions

  February 21, 2014 at 2:25 am

  #283820

  Hi T-SQL dons,

  Here is an interesting situation while dates calculation

  Consider a scenario of any periodic payments where in due date is involved.

  Let us say , mobile bill payment,

  If current due date = 30-Jan-2014, and if payment frequency = Monthly, then what is next due date ?

  Obviously , in sql I use following query

  DATEADD( MM, 1, CurrentDueDate)

  which gives me 28-Feb-2014 which is correct.

  But, for the next month the above sql statement will give me "28-Mar-2014" which is wrong. The output should be "30-Mar-2014".

  Could some one put a focus on this date calculation logic?

• Dohsan

  SSCrazy

  Points: 2972

  More actions

  February 21, 2014 at 2:46 am

  #1691018

  I'm assuming you would like the last day of the month? (March 31st?)

  SELECTDATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+N,0))

  FROMDBO.GetNums(1,12)

• Dohsan

  SSCrazy

  Points: 2972

  More actions

  February 21, 2014 at 2:51 am

  #1691020

  I think I may have misunderstood - if you want it to always be the 30th of each month with the exception of Feb, does what you have not work still?

  DECLARE @dt DATETIME = '20140130'

  SELECT @dt

  SELECTDATEADD(MM,N,@DT)

  FROMDBO.GetNums(1,12)

• Lempster

  SSCoach

  Points: 15651

  More actions

  February 21, 2014 at 4:07 am

  #1691044

  If you use a Tally table the query is simply:

  SELECTDATEADD(dd,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE())+N,0))

  FROMdbo.Tally

  You can create and populate your Tally table using this code (taken from Jeff Moden's article about Tally tables [/url]which is a must-read):

  --===== Create and populate the Tally table on the fly

  SELECT TOP 1000 IDENTITY(INT,1,1) AS N

  INTO dbo.Tally

  FROM Master.dbo.SysColumns sc1

  CROSS JOIN Master.dbo.SysColumns sc2

  --===== Add a Primary Key to maximize performance

  ALTER TABLE dbo.Tally

  ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

  Regards

  Lempster