Difference between two dates ignoring the year

Question

Post reply

Difference between two dates ignoring the year

0409SQL

SSChasing Mays

Points: 650
More actions
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

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

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

David Burrows SSC Guru Points: 65144 More actions · Answer 1

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.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

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

ScottPletcher SSC Guru Points: 101136 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

TomThomson SSC Guru Points: 104773 More actions · Answer 4

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

0409SQL SSChasing Mays Points: 650 More actions · Answer 5

0409SQL

SSChasing Mays

Points: 650

November 27, 2013 at 5:03 am

#1669415

e

0409SQL SSChasing Mays Points: 650 More actions · Answer 6

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

0409SQL SSChasing Mays Points: 650 More actions · Answer 7

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

0409SQL SSChasing Mays Points: 650 More actions · Answer 8

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

ScottPletcher SSC Guru Points: 101136 More actions · Answer 9

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

0409SQL SSChasing Mays Points: 650 More actions · Answer 10

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 11

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