Date and Time Functions

Click the link to navigate to an article on each function or check out the video on how to use a date and time function.

Watch the video

Note: Set the Locale for date and time functions to correctly represent your dates. The default is set to English (United States).

Function

Definition

COUNT_DAYS Returns the number of whole days between the given start and end dates, excluding any identified dates.

Start date and end date parameters must be in Unix time (numeric) format.

example: COUNT_DAYS( 1420070400, 1420416000 ) returns 5
DATE Converts a date parameter (must be a text date string) to Unix time (numeric) format.

example: DATE( 01/01/2015, dd/MM/yyyy ) returns 1420070400
DATE_ADD Adds or subtracts the given unit amount to the date parameter ( must be in Unix time format ) and returns the resulting date in Unix time format. To subtract, set amount to a negative value.

example: DATE_ADD( TODAY(), day, -7 ) returns the Unix time value for 1 week ago
DATE_CLOSEST Returns the date (in Unix time format) of the dow (day of week) closest to the date given as a parameter (in Unix time format). Direction is either forward (default) or backward.

example: DATE_CLOSEST( 1420070400, Sunday ) returns 1420329600
DATE_CONVERT Converts a date from one text date string format to another text date string format.

example: DATE_CONVERT( 01/01/15, dd/MM/yy, MMM dd, yyyy) returns Jan 01, 2015
DATE_ENDOF Returns the date (in Unix time format) at the end of the given unit (week, month, quarter, year) containing the date provided (in Unix time format).

example: DATE_ENDOF( 1420070400, week ) returns 1420070400
DATE_IN Returns true if the date provided (in Unix time format) is contained in the same time period specified by unit (day, week, month, quarter, year) as the relative time period (default is 0) and returns false otherwise.

example: DATE_IN ( 1420070400, month, -2 ) returns true if 1420070400 is in the same month as 2 months ago.
DATERANGE Returns all dates between the specified start and end dates. The start and end dates must be in the same format, specified by format.

example: DATERANGE( 01012015, 28022015, ddMMyyyy)
DATE_SET Sets time units to specific values for each of the given dates.
DATE_STARTOF Returns the date (in Unix time format) at the start of the given unit (week, month, quarter, year) containing the date provided (in Unix time format).

example: DATE_STARTOF( 1420070400, week ) returns 1419724800
DATE_UNITVALUE Retrieves the value of the given unit from the given dates.
DATEVALUE Converts a Unix time value to a text date string in the specified format.

example: DATEVALUE( 1420070400, dd MMM yy ) returns 01 Jan 15
NOW Returns the current date and time in Unix time.
TODAY Returns the current date for today in Unix time.
YESTERDAY Returns the current date for yesterday in Unix time.