A very useful function for measuring age of files, rows, transactions, etc. is the DATEDIFF() function. Its syntax is simple. It requires only two parameters. The old date and the newer date. The newer date should be first for positive numbers. It should be noted also that it is only the dates which are measured. The time is not included in the calculation.

DATEDIFF(more_recent_date, earlier_date)

The DATEDIFF() function is often used in conjunction with the NOW() function. If we wanted to know how long it has been since we entered a specific entry in our fict_chars table the query might look like this:

1Jane Porter2011-12-27 10:35:40
2Scarlet O'Hara2011-12-28 00:00:00
3Hermione Granger2011-12-29 15:25:27
SELECT DATEDIFF(NOW(), dts) AS days_passed FROM fict_chars

The result would be a simple one column table:


Note that Hermione Granger managed a time turning spell and her dts is in the future (from our query). Thus the result is negative.

On some SQL servers, the syntax will be slightly different:

DATEDIFF(part_of_date, start_date, ending_date)

The part_of_date parameter may be day, year, month, minutes, seconds, etc. This makes the function more flexible, but less succinct.

< COUNT Function SQL | EXPORT SET Function SQL >

Interesting Pages