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 |
|
chriskhan2000
Aged Yak Warrior
544 Posts |
 Posted - 2005-07-01 : 15:00:09
|
I'm looking at a report that we have in FoxPro and they we're doing date calculation. For example:Years = date1 - date2Years2 = Years/365 I was wondering how it would be possible to do that within T-SQL. I don't think doing the following would work.(date1 - date2) as Date Is there a date function or calculation that we can do? I think what it's trying to do is find the date between date 1 to date2 and then divide that by 365. |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-07-01 : 15:27:26
|
| Looks like it's trying to calculate the number of years between two dates. The T-SQL direct equivalent would be something like this:SELECT FLOOR(DATEDIFF(d, @date1, @date2)/365.00)But because of leap years, that can be a little imprecise. Here is a way that is more verbose, but I think should be more accurate:SELECT CASE WHEN MONTH(@date1) < MONTH(@date2) THEN DATEDIFF(yy, @date1, @date2)WHEN MONTH(@date1) = MONTH(@date2) AND DAY(@date1) <= DAY(@date2) THEN DATEDIFF(yy, @date1, @date2)ELSE DATEDIFF(yy, @date1, @date2) -1 END |
 |
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-07-12 : 15:52:18
|
WOW!! Thanks for the help. However I can't seem to get the exact data from the FoxPro Reports. It seems like it takes date1 minus date2 and then divide it by days in a year. Not sure what's going on. Here's the formula use and date use with output.FoxPro:Data Use:date1 = 3-9-2005 date2 = 9-11-2003years = date1 - date2years2 = years/365If years2> 0 annual_use = date2/years2Else annual_use = 0Endif Result: .000116272 |
|
|
|
|
|
|
Date calculation?