Built-in Functions → Date Functions

The date functions adjust the values of type date or timestamp. Here is a list of supported date functions:

Functions Adding or Subtracting Time From a Date

FunctionSignatureDescription
addMilliseconds()addMilliseconds(date exp, int milliseconds)Returns the sum of date exp and int milliseconds as a date value
addSeconds()addSeconds(date exp, int seconds)Returns the sum of date exp and int seconds as a date value
addMinutes()addMinutes(date exp, long minutes)Returns a modified date after the specified minutes have been added
addHours()addHours(date exp, int hours)Returns the sum of date exp and int hours as a date value
addDays()addDays(date exp, int days)Returns a new date after adding an integer number of days to a given date
addMonths()addMonths(date exp, int months)Returns the sum of date exp and int months as a date value
addQuarters()addQuarters(date_timestamp exp, int quarters)Returns a modified date or timestamp after the specified quarters have been added
addYears()addYears(date exp, int years)Returns the sum of date exp and int years as a date value
FunctionSignatureDescription
date()date(string exp)Returns a date value converted from a string
formatDate()formatDate(timestamp expr t, string format)Returns the date as a string formatted according to specification

Functions for Returning Time Field Values from Strings and Timestamps

FunctionSignatureDescription
minute()minute(field date)Returns the minute field of a date or timestamp value
hour()hour(field date)Returns the hour field of a date or timestamp value
day()day(date exp)Returns the day of the month
month()month(date exp)Returns the month field of a date or timestamp value
year()year(date exp)Returns the year field of a date or timestamp value

Functions for Returning the Interval Between Two Times

FunctionSignatureDescription
daysBetweendaysBetween(date exp, date exp)Returns the number of days between two dates, day1 and day2
timeBetweentimeBetween(timestamp endTime, timestamp startTime)Returns the interval between two given times, endTime and startTime

Functions for Returning Day or Month Number from a Timestamp or Date Value

FunctionSignatureDescription
weekday()weekday(date exp)Returns the week number from the year in the argument
weeknum()weeknum(date exp)Returns the day number of the week in the argument

Functions for Returning the Start or End Date

FunctionSignatureDescription
quarterStartDate()quarterStartDate()Returns the date of the first day of the current quarter
quarterEndDate()quarterEndDate()Returns the date of the last day of the current quarter
monthStartDate()monthStartDate()Returns the date of the first day of the current month
monthEndDate()monthEndDate()Returns the date of the last day of the current month
weekStartDate()weekStartDate()Returns the date of the first day of the current week
weekEndDate()weekEndDate()Returns the date of the last day of the current week

Miscellaneous Functions

FunctionSignatureDescription
removeTime()removeTime(Timestamp expression)Returns a timestamp value with the time fields removed
dateTrunc()dateTrunc(date exp, string part)Returns date exp truncated to remove specified smaller units
formatDuration()formatDuration(int duration)Converts a time value into a duration