DateDiff SQL Function
The DateDiff() function takes three (3) arguments and returns the difference between two dates in requested format. It is written as Date1 - Date2 in ???? where ???? is day, month, year, hours, minutes,
seconds, millisecond or ticks. For the purpose of code readibility, "day" and "days" have same meaning you can use "s" or without "s" to write cleaner code
e.g. (...1, 'Day') and (...4 , 'Days') or (.... 1 , 'Days').
It's Syntax for this function is DateDiff(Date1 , Date2 , Modifier Format) . The valid values for modifier are:
| Modifier |
Explanation |
| years |
Number of years added/subtracted to the date |
| months |
Number of months added/subtracted to the date |
| days |
Number of days added/subtracted to the date |
| hours |
Number of hours added/subtracted to the date |
| minutes |
Number of minutes added/subtracted to the date |
| seconds |
Number of seconds added/subtracted to the date |
| millisecond |
Number of seconds (and fractional seconds) added/subtracted to the date |
| ticks |
Number of ticks to add or subtract, see DateTime.Now.Ticks and TimeSpan.TicksPerDay in .NET on MSDN. |
/* Following will output -1 year in minus */
SELECT DateDiff( Date('Now', '+1 year') , Date() , 'year');
/* Following will output -48 months in minus */
SELECT DateDiff( Date('Now', '+4 year') , Date() , 'months');
/* Following will output 4 days */
SELECT DateDiff( Date('Now', '+4 days') , Date('Now') , 'days');
/* Following will output 30 notice DateTime instead of just Date
to get time part from datetime, return value is 30 seconds */
SELECT DateDiff( DateTime('Now', '+30 seconds') , DateTime('Now') , 'seconds');
DateDiff can find difference in date and time in several different manners. The above examples are using Date and DateTime functions to add and remove time period to show DateDiff flexibility when working with date, datetime and time only values.