

Grasshopper
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
20130111 00:00:00.000
20130211 00:00:00.000
20130311 00:00:00.000
20130411 00:00:00.000
20130511 00:00:00.000
20130611 00:00:00.000
20130711 00:00:00.000
20130811 00:00:00.000
20130911 00:00:00.000
20131011 00:00:00.000
20131111 00:00:00.000
20131211 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 20131109, then it should come up as 2 days. Also if the system date is 20141109, then it should show as 2 days. How can this be achieved? Please help




SSCertifiable
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.




SSCrazy
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




SSCommitted
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.




SSCrazy 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




Grasshopper
Group: General Forum Members
Last Login: Friday, April 11, 2014 1:02 AM
Points: 11,
Visits: 97





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




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




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




SSCommitted
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.



