DateDiff() Logic

[jalbao]

I'm not sure if I am doing something wrong, or if sqlserver's datediff() function doesn't calculate the way that I expect it to.

Example:

select datediff(yy,{ts '2002-12-31 00:00:00'},{ts '2003-01-01 00:00:00'});

The statement above returns a value of "1". Which, in my opinion, is not what the value should be. Intuitively, I would expect a value of "0" to be returned due to the fact that the difference of the two dates is only "1 day" not "1 year". Apperantly, sqlserver simply finds the difference of the years of each datetime value and disregards the months/days/time.

Assuming that this is simply the way SqlServer implements the datediff() function, does anyone know of a work-around or another function that when I calculate the difference of two dates the entire datestamp is used in the calculation - not just the datepart?

tia

 

[cheyney]

you could find the number of days different, and then divide by 365

??

cheyney

 

[scking]

You are using the wrong format for the DateDiff function. The help file may answer your questions but:

Returns a Variant (Long) specifying the number of time intervals between two specified dates.
Syntax
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])

Dim Msg As String
Dim TheDate As Date
TheDate = InputBox("Enter a date&quot
Msg = "Days from today: " & DateDiff("d", Now, TheDate)
MsgBox Msg

You will note that the result is a Long Integer containing a number that is +-Days, Weeks, Months, Years difference from date1 to date2.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------

 

[jalbao]

cheyney,
you're abosolutely right, but then I have to deal with the leap year problem.

Also, the example that I gave on the intitial post was purely an example. My question was more towards how sql server does it's datediff calculation. In other words, the same problem occurs no matter what datepart I use - sql server only calculates the difference of the datepart itself.. not the entire date/time stamp.

select datediff(d,{ts '2002-12-30 03:00:00'},{ts '2002-12-31 01:00:00'});

the example above returns a value of "1" - implying "one day". But technically, the difference of the two dates is only "22 hours", which is obviously not equal to "1 day".

The sql server datediff() method poses a problem for what my app requires. This method will require me to implement a bunch of conditions which I was hoping to avoid.

But either way, I appreciate your reply - thanks.

 

[tlbroadbent]

Datediff "returns the number of date and time boundaries crossed between two specified dates." (Source: SQL BOL) There is 1 year boundary in your example. That is why the value 1 is returned. There is also 1 day and 1 month boundary. If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[cheyney]

you said if you use my example you have to deal with leap years. If you don't measure based on boundaries like terry pointed out, but instead on the actual days in a year (or days in a month or whatever), then your definition of year will change depending on when you're measuring it. 365 days may show up as 1.000 years if not in a leap year or .997 years if they're wholely within a leap year. But they're still the same number of days. I guess its just an inherant problem in trying to measure things in terms of inconsistent units like years and months. You could always display something concrete, like hours and then show an approximation for months and years. Depending on your application you might want to use seconds in your datediff call, and then do the appropriate calculations from there.

cheyney