Cloud Software Group, Inc. EBX®
Documentation > Developer Guide > SQL in EBX®
Navigation mode Documentation > Developer Guide > SQL in EBX®

# Date and time functions

The following table lists all of the SQL date and time functions supported by EBX®, along with their standard SQL syntax. Some functions can have optional parameters: they are surrounded by square brackets.

Operator syntax | Description and example(s)
--- | ---
CURRENT_TIME | Returns the current time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE
CURRENT_DATE | Returns the current date in the session time zone, in a value of datatype DATE
CURRENT_TIMESTAMP | Returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE
Cast strings to timestamps | Note that datetime values displayed in EBX (UI and export files) are in local time zone, whereas the timestamp literals created by the SQL parser are in UTC; therefore if the server is not in UTC, you might need to create the literals on the local time zone to avoid this mismatch and get the expected results (see third example). You should also consider if the datetime value has millisecond precision (which is not displayed by EBX UI) in order to write your filter (see last example). `SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=TIMESTAMP'2017-06-28 18:06:12' : 0` `SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=CAST('2017-06-28 18:06:12' AS TIMESTAMP) : 0` `SELECT COUNT(*) FROM employee WHERE lastProfileUpdateTime=CAST('2017-06-28 18:06:12 Europe/Paris' AS TIMESTAMP WITH LOCAL TIME ZONE) : 1` `SELECT COUNT(*) FROM employee e WHERE e."ebx-metadata"."system"."update_time">=TIMESTAMP'2023-08-28 09:56:55' AND e."ebx-metadata"."system"."update_time"<TIMESTAMP'2023-08-28 09:56:56' : 1`
YEAR(date) | Extracts and returns the value of the year from a datetime value expression. Returns an integer. `SELECT YEAR(TIMESTAMP '1971-07-20 09:34:21') : 1971` `SELECT YEAR(DATE '1968-07-20') : 1968` `SELECT YEAR(hiringDate) FROM employee WHERE name='Smith' : 2015` `SELECT YEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 2017`
QUARTER(date) | Extracts and returns the value of the quarter from a datetime value expression. Returns an integer between 1 and 4. `SELECT QUARTER(TIMESTAMP '1971-07-20 09:34:21') : 3` `SELECT QUARTER(hiringDate) FROM employee WHERE name='Smith' : 4` `SELECT QUARTER(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 2`
QUARTER(date) | Extracts and returns the value of the month from a datetime value expression. Returns an integer between 1 and 12. `SELECT MONTH(TIMESTAMP '1971-07-20 09:34:21') : 7` `SELECT MONTH(hiringDate) FROM employee WHERE name='Smith' : 10` `SELECT MONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 6`
WEEK(date) | Extracts and returns the value of the week from a datetime value expression. Returns an integer between 1 and 53. `SELECT WEEK(TIMESTAMP '1971-07-20 09:34:21') : 29` `SELECT WEEK(hiringDate) FROM employee WHERE name='Smith' : 42` `SELECT WEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 26`
DAYOFYEAR(date) | Extracts and returns the value of the day of year from a datetime value expression. Returns an integer between 1 and 366. `SELECT DAYOFYEAR(TIMESTAMP '1971-07-20 09:34:21') : 201` `SELECT DAYOFYEAR(hiringDate) FROM employee WHERE name='Smith' : 287` `SELECT DAYOFYEAR(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 179`
DAYOFMONTH(date) | Extracts and returns the value of the day of month from a datetime value expression. Returns an integer between 1 and 31. `SELECT DAYOFMONTH(TIMESTAMP '1971-07-20 09:34:21') : 20` `SELECT DAYOFMONTH(hiringDate) FROM employee WHERE name='Smith' : 14` `SELECT DAYOFMONTH(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 28`
DAYOFWEEK(date) | Extracts and returns the value of the day of week from a datetime value expression. Returns an integer between 1 and 7. `SELECT DAYOFWEEK(TIMESTAMP '1971-07-20 09:34:21') : 3` `SELECT DAYOFWEEK(hiringDate) FROM employee WHERE name='Smith' : 4` `SELECT DAYOFWEEK(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 4`
HOUR(date) | Extracts and returns the value of the hours from a datetime value expression. Returns an integer between 0 and 23. `SELECT HOUR(TIMESTAMP '1971-07-20 09:34:21') : 9` `SELECT HOUR(TIME '10:34:21') : 10` `SELECT HOUR(workStart) FROM employee WHERE name='Smith' : 8` `SELECT HOUR(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 16`
MINUTE(date) | Extracts and returns the value of the minutes from a datetime value expression. Returns an integer between 0 and 59. `SELECT MINUTE(TIMESTAMP '1971-07-20 09:34:21') : 34` `SELECT MINUTE(TIME '10:35:21') : 35` `SELECT MINUTE(workStart) FROM employee WHERE name='Smith' : 0` `SELECT MINUTE(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 6`
SECOND(date) | Extracts and returns the value of the seconds from a datetime value expression. Returns an integer between 0 and 59. `SELECT HOUR(TIMESTAMP '1969-07-20 09:34:21') : 9` `SELECT SECOND(TIME '10:34:22') : 22` `SELECT SECOND(workStart) FROM employee WHERE name='Smith' : 0` `SELECT SECOND(lastProfileUpdateTime) FROM employee WHERE name='Smith' : 12`

Documentation > Developer Guide > SQL in EBX®