Post reply

Difference between two dates ignoring the year

  • November 26, 2013 at 9:12 am

    #280501

    Hi

    I have a date field in the database called cutoffdate. The table name is Paydate. The cutoffdate is as shown below:

    CutoffDate

    2013-01-11 00:00:00.000

    2013-02-11 00:00:00.000

    2013-03-11 00:00:00.000

    2013-04-11 00:00:00.000

    2013-05-11 00:00:00.000

    2013-06-11 00:00:00.000

    2013-07-11 00:00:00.000

    2013-08-11 00:00:00.000

    2013-09-11 00:00:00.000

    2013-10-11 00:00:00.000

    2013-11-11 00:00:00.000

    2013-12-11 00:00:00.000

    I want to compare the current date with the cutoffdate (any of the 12 dates above) and then if the difference is 2 days, I need to proceed further.

    This cutoffdate will remain same next year and the year after. so i need to compare the date ignoring the year part. For example if the system date is 2013-11-09, then it should come up as 2 days. Also if the system date is 2014-11-09, then it should show as 2 days. How can this be achieved? Please help

  • November 26, 2013 at 9:46 am

    #1669202

    Is it exactly 2 days?

    Is it +/- 2 days?

    How many rows?

    One way would be to calculate the day number +/- 2 for the current date and compare that to the calculated day number of custoffdate

    p.s. DATEPART using dayofyear will give you day number

    Far away is close at hand in the images of elsewhere.
    Anon.

  • November 26, 2013 at 10:46 am

    #1669216

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • November 26, 2013 at 12:17 pm

    #1669241

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    I don't think the first quite works, because of leap years.

    The second should work fine if you get rid of the ABS() function.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • November 26, 2013 at 4:44 pm

    #1669287

    ScottPletcher (11/26/2013)

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    I don't think the first quite works, because of leap years.

    The second should work fine if you get rid of the ABS() function.

    Actually we don't know whether the ABS is needed or not - strictly speaking "with 2 days of such and such a date" means a 4 day wide band centred on the spei=cified date, so ABS is needed, but that may have been a sloppy statement of teh requirement so maybe ABS is not needed (and maybe it's the two days after that matter, so if abs isn't needed we don't know whether 0 to -2 or 0 to 2 is the result that would tell us to do something).

    Tom

  • November 27, 2013 at 5:03 am

    #1669415

    e

  • November 27, 2013 at 5:04 am

    #1669417

    David Burrows (11/26/2013)

    Is it exactly 2 days?

    Is it +/- 2 days?

    How many rows?

    One way would be to calculate the day number +/- 2 for the current date and compare that to the calculated day number of custoffdate

    p.s. DATEPART using dayofyear will give you day number

    it should be getdate() - cutoffdate = -2

    So ideally it must be 2 days behind. For example, if the date is 2013/12/09 then it should determine it is close to cutoff date, i.e, 2 days behind cutoff date

  • November 27, 2013 at 5:07 am

    #1669419

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    Thanks, it is partially right. But if the date is 2013/12/13, it would still show as 2.

    I need getdate() -curoffdate = -2. Sorry didnt mention that in my original question.

    So ideally if it is 2013/12/09 , the difference must be flagged. Not when it is 2013/12/13. Hope it makes sense !

    Thanks

  • November 27, 2013 at 5:11 am

    #1669421

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    Thanks, it does work. Please ignore earlier message, removed ABS()

  • November 27, 2013 at 8:23 am

    #1669496

    L' Eomot Inversé (11/26/2013)

    ScottPletcher (11/26/2013)

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    I don't think the first quite works, because of leap years.

    The second should work fine if you get rid of the ABS() function.

    Actually we don't know whether the ABS is needed or not - strictly speaking "with 2 days of such and such a date" means a 4 day wide band centred on the spei=cified date, so ABS is needed, but that may have been a sloppy statement of teh requirement so maybe ABS is not needed (and maybe it's the two days after that matter, so if abs isn't needed we don't know whether 0 to -2 or 0 to 2 is the result that would tell us to do something).

    In general perhaps, but not in this case; a later date would extremely unlikely for a cutoffdate, right? 🙂

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • November 27, 2013 at 8:34 am

    #1669500

    ScottPletcher (11/26/2013)

    Luis Cazares (11/26/2013)

    Here are 2 options to perform your calculation:

    SELECT ABS(DATEPART( dayofyear, CutoffDate) - DATEPART( dayofyear, GETDATE())),

    ABS(DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, GETDATE()), CutOffDate), GETDATE()))

    FROM #Temp

    I don't think the first quite works, because of leap years.

    The second should work fine if you get rid of the ABS() function.

    Yeap ! you are right. The first one doesn't work for leap year. The second one is perfect:

    SELECT

    DATEDIFF( day, DATEADD( year, DATEDIFF( year, CutOffDate, @currentdate), CutOffDate), @currentdate) as diff from #temp

  • November 27, 2013 at 8:53 am

    #1669504

    Now the question is, do you understand how this works?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

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

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