Post reply

DateAdd Function on 30th and 31st of any month

  • November 27, 2006 at 2:24 pm

    #117816

    Hi Everyone,

    When I run the dateadd function adding months on 30th and 31st of any

    months returns the same result. I am OK with that because logically it

    is correct.

    If I have to do a reverse lookup for the date, how can I do that.

    Here is the code sample...

    select dateadd(mm,6,'2006-05-30 00:00:00.000')

    select dateadd(mm,6,'2006-05-31 00:00:00.000')

    -- 2006-11-30 00:00:00.000

    -- 2006-11-30 00:00:00.000

    select dateadd(mm,-6,'2006-11-30 00:00:00.000')

    select dateadd(mm,-6,'2006-12-01 00:00:00.000')

    --2006-05-30 00:00:00.000

    --2006-06-01 00:00:00.000

    The problem I have is when I do a reverse lookup I am missing all the

    records which got created on

    2006-05-31 00:00:00.000.

    Thanks in Advance....

    Thanks,

    Ganesh

  • November 27, 2006 at 2:27 pm

    #674170

    Maybe you can change the way you approach the problem.  What is the business requirement?

  • November 27, 2006 at 2:29 pm

    #674171

    You don't need 30th ot 31st date. You need LAST date of the month.

    Last day of the month is ALWAYS the one day before the 1st of the next month.

    1st is 1st for every month.

    Have you got it or I need to continue?

    _____________
    Code for TallyGenerator

  • November 27, 2006 at 2:33 pm

    #674173

    Thanks for the reply

    The requirement is like this. I have a table

    Policy_Term

    policyid      policy_term_id     valid_start_date    valid_end_date  

        X123             1                05/31/2006           NULL

        x123              2                11/30/2006          NULL

    This is how the data looks, the same table has the valid_bit column. I have to update the valid_bit to 0 for the previous term.

    It's basically a insurance firm, all policies gets renewed every 6 months.

    Please assist

     

     

     

  • November 27, 2006 at 2:45 pm

    #674175

    Sergiy was on it. The way you want to do it is move to the first of the start month, then add seven months, then back up a single day.

    That will give you the last day of the month six months from the start date.

  • November 27, 2006 at 2:53 pm

    #674179

    Example script below... there's probably a cleaner way to do this but this is what I just threw together:

    DECLARE @date DATETIME

    SET @date = GETDATE() -- or whatever date

    -- 1 - DATEPART(dd, @date) will give you the first of the month.

    SELECT DATEADD(dd, 1 - DATEPART(dd, @date), @date)

    -- Then move forward 7 months.

    SELECT DATEADD(mm, 7, DATEADD(dd, 1 - DATEPART(dd, @date), @date))

    -- Finally subtract a single day to find the last day of the month, 6 months from the start.

    SELECT DATEADD(dd, -1, DATEADD(mm, 7, DATEADD(dd, 1 - DATEPART(dd, @date), @date)))

  • November 27, 2006 at 3:06 pm

    #674182

    Thanks to everyone....

    I hope this looks very easy to me....

    If the date is last day of the month {

    SELECT dateadd(DAY, -1, dateadd(MONTH, -6, dateadd(DAY, 1, @date)))

    }

    else{

    SELECT dateadd(MONTH,-6,@date)

    }

    Please correct me If I am missing something

    Thanks,

    Ganesh

     

  • November 27, 2006 at 4:04 pm

    #674193

    You are probably missing the case of February.

    You better use :

    IF DAY(@date) <>  DAY(dateadd(MONTH,-6,@date))

    ....

    _____________
    Code for TallyGenerator

  • November 28, 2006 at 2:14 pm

    #674535

    Yep. Thanks for the input.....

    Finally I got this.....Could you please take a look and let me know if I am missing other than this....

    declare @inputdate datetime

        set @inputdate =  '11/30/06 0:00'

     

    if datediff (mm,@inputdate, @inputdate + 1 ) = 1 and datepart(day,@inputdate) in (30,29,28)

      select dateadd(day, -1, dateadd(month, -6, dateadd(day, 1, @inputdate)))

    else if datediff (mm,@inputdate, @inputdate + 1 ) = 1 and datepart(day,@inputdate) = 31

      select dateadd(day, -1, dateadd(month, -6, dateadd(day, -1, @inputdate)))

    else

      select dateadd(mm,-6,@inputdate)

  • November 28, 2006 at 2:52 pm

    #674541

    Try it for

    set @inputdate =  '12/31/06 00:00'

    _____________
    Code for TallyGenerator

  • November 28, 2006 at 10:41 pm

    #674577

    try calculating the first day of the month following and the subtract 1 minute, you'll have the last day of the month irregardless if it is 30, 31 , 29, or 28.

    try this:

    select dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate() )+1, 0))

    (for the last day of the current month but you can substitute for getdtae())

  • February 16, 2011 at 8:46 am

    #1287730

    All this date math is fun but wouldn't it be easier to update the valid bit for all

    records that have the same policyID and a Start Date that is less than 11/30/2006.

    My guess is that only one policy should be active for the same ploicyID.

    Do we need to know the start date of the old policy or just that it is older than the current one?

  • February 16, 2011 at 10:01 am

    #1287777

    Please note: 5 year old thread.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 13 posts - 1 through 12 (of 12 total)

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