Does anyone know how DATEDIFF works, in SQL Anywhere?

To put it another way, do **you** know exactly what DATEDIFF does, for all the date units: year, month, day and so on? Or even what it does for **some** of the date units?

As it turns out, I didn't, not exactly. And because DATEDIFF has appeared so many times in this blog, it's time for a closer look... especially after publishing Should Examples Work?

Here's what the Help says about DATEDIFF:

DATEDIFF hasn't just been usedin this blog, it's been a cornerstone feature of how-to posts like Today's Tip: Counting Days of the Week and Everything Looks Like a Database as well as the subject of in-depth discussions like:

- EXPRTYPE, DATEDIFF, MICROSECOND, BIGINT and Volker Barth announced that starting with Version 12 DATEDIFF returns BIGINT instead of INTEGER for hour, minute, second, millisecond and microsecond date parts.

- Let's play "Gotcha!" - Round Two warned "don't let your SQL code stray outside the true useful range for the TIMESTAMP data type which is 1600-02-28 23:59:59 to 7910-12-31 23:59:59".

- Let's play "Gotcha!" - Round Three warned "don't let the fact that DATEDIFF has been enhanced to return BIGINT values lead you to assume that DATEADD will accept a BIGINT... it won't; you're stuck using INTEGER values."

- Beware CURRENT TIMESTAMP talked about how Windows might reset CURRENT TIMESTAMP backwards in time if a drifting system clock can't be handled by adjusting the clock rate.

Here's what the Help

This function calculates the number of date parts between two specified dates. The result is a signed integer value equal to (date-expression-2 - date-expression-1), in date parts.

The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part.

When you usedayas the date part, the DATEDIFF function returns the number of midnights between the two times specified, including the second date but not the first.

When you usemonthas the date part, the DATEDIFF function returns the number of first-of-the-months between two dates, including the second date but not the first.

When you useweekas the date part, the DATEDIFF function returns the number of Sundays between the two dates, including the second date but not the first.

**should**say:

The following example shows all the DATEDIFF calculations for two timestamps that are exactly one microsecond (0.000001 second) apart. It shows

DATEDIFF ( year, date-expression-1, date-expression-2 ) returns the integer number ofyear boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (quarter, date-expression-1, date-expression-2 ) returns the integer number ofquarter boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (month, date-expression-1, date-expression-2 ) returns the integer number ofmonth boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (week, date-expression-1, date-expression-2 ) returns the integer number ofweek boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (day, date-expression-1, date-expression-2 ) returns the integer number ofday boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (dayofyear, date-expression-1, date-expression-2 ) returns the integerdayofyear difference= DATEPART ( dayofyear, date-expression-2 ) - DATEPART ( dayofyear, date-expression-1 ).

DATEDIFF (hour, date-expression-1, date-expression-2 ) returns the bigint number ofhoursbetween date-expression-1 and date-expression-2.

DATEDIFF (minute, date-expression-1, date-expression-2 ) returns the bigint number ofminute boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (second, date-expression-1, date-expression-2 ) returns the bigint number ofsecondsbetween date-expression-1 and date-expression-2.

DATEDIFF (millisecond, date-expression-1, date-expression-2 ) returns the bigint number ofmillisecond boundariesbetween date-expression-1 and date-expression-2.

DATEDIFF (microsecond, date-expression-1, date-expression-2 ) returns the bigint number ofmicrosecondsbetween date-expression-1 and date-expression-2.

- that almost all of them return the number of unit boundaries (1 for year, quarter, etc),

- two return the number of units (0 for hour and second),

- one (microsecond) effectively returns the number of units because it's at the limit of timestamp precision so the difference between "unit" and "unit boundary" is moot, and

- one (dayofyear) returns a simple difference that disregards the context altogether; the value -364 might look funky but it makes sense when you consider the definition above.

Whether the difference between "units" and "number of unit boundaries" is important depends on what your code is trying to do.

SELECT DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year, DATEDIFF ( quarter, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS quarter, DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month, DATEDIFF ( dayofyear, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS dayofyear, DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day, DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week, DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour, DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute, DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second, DATEDIFF ( millisecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS millisecond, DATEDIFF ( microsecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS microsecond; year quarter month dayofyear day week hour minute second millisecond microsecond 1 1 1 -364 1 1 0 1 0 1 1

For example, if you're trying to calculate age, then DATEDIFF really sucks; it only gets the right answer half the time.

Want proof? Here it is...

Since many (most?) uses of DATEDIFF are a variation on the age calculation (number of days, seconds, whatever), the "unit boundary" calculation may indeed have serious implications for program (in)correctness. Sadly, none of the examples in the DATEDIFF Help topic demonstrate how the unit boundary calculation differs from one that counts actual units... but other examples in the Help may be (adversely?) affected by it (e.g., Should Examples Work?)

If a baby was born on 2012-07-27 and the CURRENT DATE is 2013-07-28, DATEDIFF is correct in telling us the baby's age is 1. A baby born two days later, however, has not reached its first birthday so DATEDIFF is wrong. Conclusion: DATEDIFF might be of assistance to underage drinkers, but otherwise it isn't much use for calculating age.SELECT DATEDIFF ( year, '2012-07-27', '2013-07-28' ) AS correct_age, DATEDIFF ( year, '2012-07-29', '2013-07-28' ) AS incorrect_age; correct_age incorrect_age 1 1

### Need a laugh?

That big ugly example above comes from the Microsoft SQL Server version of DATEDIFF:

If you like [cough] consistency, you should love SQL Server: no more zeroes, no more -364 :)

1> SELECT DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year, 2> DATEDIFF ( quarter, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS quarter, 3> DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month, 4> DATEDIFF ( dayofyear, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS dayofyear, 5> DATEDIFF ( day, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day, 6> DATEDIFF ( week, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week, 7> DATEDIFF ( hour, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour, 8> DATEDIFF ( minute, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute, 9> DATEDIFF ( second, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second, 10> DATEDIFF ( millisecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS millisecond, 11> DATEDIFF ( microsecond, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS microsecond 12> GO year quarter month dayofyear day week hour minute second millisecond microsecond ---- ------- ----- --------- --- ---- ---- ------ ------ ----------- ----------- 1 1 1 1 1 1 1 1 1 1 1

## No comments:

Post a Comment