DateTime Functions
In the previous article I described some of the date and time functions provided by Transact-SQL (T-SQL). Those functions allowed specific parts of a DateTime or SmallDateTime value to be extracted as integer or NVarChar data. This follow-up article describes two further functions that permit DateTime information to be manipulated. The first of these enables you to add or subtract values from a date or time. The second calculates the difference between two times.
DateAdd
The DateAdd function is used to add an amount of time to a DateTime or SmallDateTime value. The unit of time to add is specified using a character code similar to those seen in the previous article. This is combined with an integer value specifying the amount of time to be added. The initial date and time is passed as the third argument to the function. The result of the operation is returned as a SmallDateTime if the input time was also a SmallDateTime, or as a DateTime otherwise.
For example, the following statement adds two days to the specified value and outputs the result. The format of the outputted date may vary according to your configuration.
PRINT dateadd(d, 2, '5 December 2009') -- Dec 7 2009 12:00AM
The function can also be used to subtract an amount of time from a value. To do so, the integer value must be negative. For example, the following sample subtracts two hours from a date and time value.
PRINT dateadd(hh, -2, '5 December 2009') -- Dec 4 2009 10:00PM
The DateAdd function can be used to add various units of time according to the chosen date part character code. The list of available character codes is as follows:
| Code | Date Part |
|---|
| yy, yyyy | Year |
| q, qq | Quarter |
| m, mm | Month* |
| dy, y | Day of Year |
| d, dd | Day |
| wk, ww | Week |
| dw | Weekday |
| hh | Hour |
| mi, n | Minute |
| s, ss | Second |
| ms | Millisecond |
*When adding a number of months to a date creates an invalid result, the date of the last day of the resultant month is returned. For example, adding one month to 31 January in the following example returns 28 February.
PRINT dateadd(m, 1, '31 January 2010') -- Feb 28 2010 12:00AM
DateDiff
The DateDiff function calculates the difference between two date and time values, returning an integer result that expresses the difference in a specified unit. The unit is one of the above character codes and is provided as the first argument. The second and third arguments are the start and end times respectively. If the start time is later than the end time, the result is negative.
We can demonstrate this by calculating the difference, in months, between two dates.
PRINT datediff(m, '1 January 2010', '1 March 2011') -- 14
PRINT datediff(m, '1 March 2011', '1 January 2010') -- -14
5 December 2009