Previously on . . . The story began with Documenting DATEDIFF and continued with three episodes about the use and abuse of DATEDIFF in this blog, in Foxhound and in the Help.
Now the story turns to seven other SQL Anywhere functions that can be used instead of DATEDIFF to compute the difference between two timestamps.
Here's a comparison of how these seven functions stack up against DATEDIFF when applied to the same two timestamp values that are exactly one microsecond apart:
SELECT 'DATEDIFF' AS "function",
DATEDIFF ( year, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
DATEDIFF ( month, '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
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
UNION ALL
SELECT 'YEARS, etc',
YEARS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS year,
MONTHS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS month,
DAYS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS day,
WEEKS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS week,
HOURS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS hour,
MINUTES ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS minute,
SECONDS ( '2011-12-31 23:59:59.9999999', '2012-01-01 00:00:00.0000000' ) AS second;
function year month day week hour minute second
-------- ---- ----- --- ---- ---- ------ ------
DATEDIFF 1 1 1 1 0 1 0
YEARS, etc 1 1 1 0 0 1 1
|
Here's what the Help has to say about these seven alternatives to DATEDIFF, and how the Help stacks up against testing:
| What The Help Says | What Testing Says | |
| YEARS | The value of YEARS is computed by counting the number of first days of the year between the two dates. | True. YEARS returns the number of year boundaries between the two parameters, just like DATEDIFF YEAR. |
| MONTHS | If you pass two TIMESTAMP values to the MONTHS function, the function returns the integer number of months between them. | False. MONTHS returns the number of month boundaries between the two parameters, just like DATEDIFF MONTH. |
| DAYS | If you pass two TIMESTAMP values to the DAYS function, the function returns the integer number of days between them. | False. DAYS returns the number of day boundaries between the two parameters, just like DATEDIFF DAY. |
| WEEKS | Given two dates (Syntax 2), the WEEKS function returns the number of weeks between them. The WEEKS function is similar to the DATEDIFF function, however the method used to calculate the number of weeks between two dates is not the same and can return a different result. The return value for WEEKS is determined by dividing the number of days between the two dates by seven, and then rounding down. However, DATEDIFF uses number of week boundaries in its computation. This can cause the values returned from the two functions to be different. For example, if the first date is a Friday and the second date is the following Monday, the WEEKS function returns a difference of 0, but the DATEDIFF function returns a difference of 1. While neither method is better than the other, you should consider the difference when choosing between WEEKS and DATEDIFF. | True. This is the best Help of all these functions, DATEDIFF included... it gives a clear explanation of the difference between counting units (weeks, etc.) and counting boundaries (week changes, etc). |
| HOURS | If you pass two TIMESTAMP values to the HOURS function, the function returns the integer number of hours between them. | True. Both HOURS and DATEDIFF HOUR return the number of hours, not the number of hour boundaries. |
| MINUTES | If you pass two TIMESTAMP values to the MINUTES function, the function returns the integer number of minutes between them. | False. MINUTES returns the number of minute boundaries between the two parameters, just like DATEDIFF MINUTE. |
| SECONDS | If you pass two TIMESTAMP values to the SECONDS function, the function returns the integer number of seconds between them as a SIGNED BIGINT value. | False. SECONDS returns the number of second boundaries between the two parameters. This is different from DATEDIFF SECOND which returns the number of seconds rather than the number of boundaries. |
No comments:
Post a Comment