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
| Function | Signature | Description |
|---|
| 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 |
| Function | Signature | Description |
|---|
| 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
| Function | Signature | Description |
|---|
| 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
| Function | Signature | Description |
|---|
| daysBetween | daysBetween(date exp, date exp) | Returns the number of days between two dates, day1 and day2 |
| timeBetween | timeBetween(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
| Function | Signature | Description |
|---|
| 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
| Function | Signature | Description |
|---|
| 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
| Function | Signature | Description |
|---|
| 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 |