Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Difference between two dates ignoring the year Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, November 26, 2013 9:12 AM
 Grasshopper Group: General Forum Members Last Login: Friday, April 11, 2014 1:02 AM Points: 11, Visits: 97
 HiI have a date field in the database called cutoffdate. The table name is Paydate. The cutoffdate is as shown below:CutoffDate2013-01-11 00:00:00.0002013-02-11 00:00:00.0002013-03-11 00:00:00.0002013-04-11 00:00:00.0002013-05-11 00:00:00.0002013-06-11 00:00:00.0002013-07-11 00:00:00.0002013-08-11 00:00:00.0002013-09-11 00:00:00.0002013-10-11 00:00:00.0002013-11-11 00:00:00.0002013-12-11 00:00:00.000I 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 Group: General Forum Members Last Login: Today @ 11:25 AM Points: 6,808, Visits: 6,296
 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 custoffdatep.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 Group: General Forum Members Last Login: Today @ 5:48 PM Points: 2,774, Visits: 5,949
 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 LeacockForum 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 Group: General Forum Members Last Login: Today @ 1:43 PM Points: 1,757, Visits: 2,574
 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 Group: General Forum Members Last Login: Today @ 3:34 PM Points: 8,295, Visits: 8,748
 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 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 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 custoffdatep.s. DATEPART using dayofyear will give you day numberit should be getdate() - cutoffdate = -2So 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 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 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 Group: General Forum Members Last Login: Today @ 1:43 PM Points: 1,757, Visits: 2,574
 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

 Permissions