An interesting scenario with Dates Calculation

  • 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?

  • 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)

  • 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)

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply