Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fralo
Posting Yak Master
161 Posts |
 Posted - 2011-01-13 : 10:11:53
|
| For each record in a table, I need to determine if the difference between a certain date field and the current date is >= 2 years.I think this involves the DATEDIFF function but I'm unsure of the syntax. Pseudocode..something like this?IF DATEDIFF(termdate, getdate()) >= 2 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 10:16:45
|
| IF DATEDIFF(day, termdate, getdate()) >= 2 |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-01-13 : 10:17:49
|
| close, try this..IF DATEDIFF(yy,termdate, getdate()) >= 2Remember though that as soon as you roll over into another year that this will count as 1, so if you run the following:select DATEDIFF(yy,getdate()-15, getdate())You will get 1, even though it is only 15 days.The way around this is to use day and set your value to 730 or 731.. |
|
|
|
fralo
Posting Yak Master
161 Posts |
Posted - 2011-01-13 : 10:24:55
|
| Do you mean that if one date is early in January and the other date is in December that this will return as 1 year? If so, would it be better then in my particular case to check for a difference in days instead of years?730 days = 2 yearsOh, I forgot to mention as well that in my database the date is stored in a nvarchar(255) field with the format of'19931127'. So I need some conversion as well.Thanks for your help. I really appreciate it. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 10:30:35
|
| If '19931127' means November 27, 1993 then it will implicitly convert to a datetime, so you'll be ok -- as long as ALL of the values are 8 digits and formatted as such.If you're looking for days, then I think you want the query I posted above. Not sure what Rick is driving at to be honest. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-01-13 : 10:42:40
|
I am saying that if you have a date of (lets say) 30 Dec 2009 and you want to see if it is greater than 2 years, then logically, it is not, yet datediff(yy,date,date2) would say that it was as the year part of date is 2 digits difference. That is why I said use days.  |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 10:58:53
|
| Gotcha |
|
|
|
|
|
|
|
|
Help with determining difference between dates