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.