Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Difference between two dates ignoring the year Expand / Collapse
Author
Message
Posted Tuesday, November 26, 2013 9:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
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
Post #1517733
Posted Tuesday, November 26, 2013 9:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:23 AM
Points: 6,795, Visits: 6,267
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.

Post #1517745
Posted Tuesday, November 26, 2013 10:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:44 PM
Points: 2,763, Visits: 5,901
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1517764
Posted Tuesday, November 26, 2013 12:17 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 1,734, Visits: 2,534
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)
I'm not fat, I'm gravity challenged.
Post #1517793
Posted Tuesday, November 26, 2013 4:44 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 4:56 PM
Points: 8,271, Visits: 8,717
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
Post #1517851
Posted Wednesday, November 27, 2013 5:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
e
Post #1518009
Posted Wednesday, November 27, 2013 5:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
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
Post #1518011
Posted Wednesday, November 27, 2013 5:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
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
Post #1518013
Posted Wednesday, November 27, 2013 5:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11, Visits: 97
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()
Post #1518015
Posted Wednesday, November 27, 2013 8:23 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:59 PM
Points: 1,734, Visits: 2,534
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)
I'm not fat, I'm gravity challenged.
Post #1518095
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse