Date & Time Functions

Date and time functions allow you to manipulate variables that have date or datetime data types.  This includes performing date addition and subtraction and extracting parts of dates, among other operations.  For converting from date to strings or vice versa, see the article:


Function Usage and description
Month Addition

Usage: ADD_MONTHS(date, number)

This function returns a date that is computed by adding the provided number of months to the provided date.

Current Date and Time

Usage: GETDATE()

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS'

Compare Dates

Usage: DATE_CMP(date1, date2)

This function returns a 0 if the provided dates are equal, a 1 if the first date is later, and a -1 if the second date is later.

Compare Date/times

Usage: TIMESTAMP_CMP(datetime1, datetime2)

This function returns a 0 if the provided datetime values are equal, a 1 if the first datetime is later, and a -1 if the second datetime value is later.

Date Arithmetic

Usage: DATEADD(datepart, number, date)

This function returns a date arrived at by adding number dateparts to the provided date.

The possible dateparts that can be specified (note that they should be specified without quotes) are (and the appropriate plural versions of these):

  • year
  • quarter
  • month
  • week
  • dow(day of week: returns 0-6 starting with Sunday)
  • doy(day of year)
  • day
  • hour
  • minute
  • second
  • millisecond
  • microsecond
  • timezone
Date Difference

Usage: DATEDIFF(datepart, date1, date2)

Returns date1 minus date2 expressed as a value in the units of the provided datepart (for example, days).

See the DATEADD function for the list of possible date parts that can be requested.

Extract a Date Part

Usage: EXTRACT(datepart FROM date)

The function returns the part of the provided date as requested by datepart. See the DATEADD function for a listing of possible date parts.

Last Day of Month

Usage: LAST_DAY(date)

Returns a date that is the last day of the month relative to the provided input date.

Months Between Two Dates

Usage: MONTHS_BETWEEN(date1, date2)

Returns as a fractional value the number of months between date1 and date2. The value is negative if the second date is after the first date.

Next Day

Usage: NEXT_DAY(date, dow string)

Returns as a date result the first occurence of the provided dow string (such as 'Tuesday' or 'Saturday') after the provided date.

Truncate Datetime Value

Usage: TRUNC(datetime)

Returns the date on which the datetime value falls.