UnityJDBC - Date and Time Functions
Summary
| Function | Syntax | Description |
|---|---|---|
| ADDDATE | ADDDATE(dateexpr, intervalexpr) | Adds an interval or number of days to a date expression. |
| ADDTIME | ADDTIME(timeexpr, intervalexpr) | Adds an interval or number of hours to a time expression. |
| ADD_MONTHS | ADD_MONTHS(dateexpr, months) | Adds a number of months to a date. |
| CONVERT_TZ | Converts a date from timezone from to timezone to. | |
| CURDATE | CURDATE() | Returns the current date in 'YYYY-MM-DD' format. |
| CURRENT_DATE | CURRENT_DATE | Returns the current date in 'YYYY-MM-DD' format. |
| CURRENT_TIME | CURRENT_TIME | Returns the current time in 'HH:MM:SS' format. |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Returns the current timestamp in 'YYYY-MM-DD HH:MM:SS.uuuuuu' format. |
| CURTIME | CURTIME() | Returns the current time in 'HH:MM:SS' format. |
| DATEADD | DATEADD(datepart, value, dateexpr) | Adds a specified datepart to a date. |
| DATEDIFF | DATEDIFF(datepart, value, dateexpr) | Returns the difference between two dates on a given datepart. |
| DAY | DAY(dateexpr) | Returns the day of the month in the range 1 to 31 of dateexpr. |
| DAYNAME | DAYNAME(dateexpr) | Returns the name of the weekday of dateexpr. |
| DAYOFMONTH | DAYOFMONTH(dateexpr) | Returns the day of the month in the range 1 to 31 of dateexpr. |
| DAYOFWEEK | DAYOFWEEK(dateexpr) | Returns the day of the week (1=Sunday) of dateexpr. |
| DAYOFYEAR | DAYOFYEAR(dateexpr) | Returns the day of the year in the range of 1 to 366 of dateexpr. |
| GETDATE | GETDATE() | Returns the current date as a value in 'YYYY-MM-DD' format. |
| HOUR | HOUR(dateexpr) | Returns the hour in the range of 0 to 23 of dateexpr. |
| MINUTE | MINUTE(dateexpr) | Returns the minute in the range of 0 to 59 of dateexpr. |
| MONTH | MONTH(dateexpr) | Returns the month for date in the range 1 to 12 for dateexpr. |
| MONTHNAME | MONTHNAME(dateexpr) | Returns the name of the month of dateexpr. |
| NOW | NOW() | Returns the current timestamp in 'YYYY-MM-DD HH:mm:ss' format. Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format. |
| QUARTER | QUARTER(dateexpr) | Returns the quarter for date in the range 1 to 4 for dateexpr. |
| SECOND | SECOND(dateexpr) | Returns the second in the range of 0 to 59 of dateexpr. |
| SYSDATE | SYSDATE | Returns the current date in 'YYYY-MM-DD' format. |
| TO_CHAR | TO_CHAR(date, formatexpr) | Converts a date into a string with a given format. |
| TO_DATE | TO_DATE(datestr, formatexpr) | Given a date format specified in Java syntax, returns a date or NULL if conversion fails. |
| WEEK | WEEK(dateexpr) | Returns the week number from 1 to 52 of dateexpr. |
| YEAR | YEAR(dateexpr) | Returns the year of dateexpr. |
Detailed Function Descriptions
ADDDATE
Syntax: ADDDATE(dateexpr, intervalexpr)
ADDDATE() will add to dateexpr either an integer number of days or an INTERVAL expression. It is not identical to DATEADD() that will add to a date expression one particular date part (hours, days, etc.).
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
 | | | | | | |
ADDTIME
Syntax: ADDTIME(timeexpr, intervalexpr)
ADDTIME() will add to timeexpr either an integer number of hours or an INTERVAL expression. It is similar to ADDDATE() except it defaults to adding hours rather than days.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
 | | | | | | |
ADD_MONTHS
Syntax: ADD_MONTHS(dateexpr, months)
ADD_MONTHS() will add to dateexpr either an integer number of months.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CONVERT_TZ
Syntax:
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CURDATE
Syntax: CURDATE()
Returns the current date as a value in 'YYYY-MM-DD' format. CURRENT_DATE is identical to CURRENT_DATE
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CURRENT_DATE
Syntax: CURRENT_DATE
Returns the current date as a value in 'YYYY-MM-DD' format. CURRENT_DATE is identical to CURDATE().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CURRENT_TIME
Syntax: CURRENT_TIME
Returns the current time as a value in 'HH:MM:SS' format. CURRENT_TIME is identical to CURTIME().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CURRENT_TIMESTAMP
Syntax: CURRENT_TIMESTAMP
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS.uuuuuu' format. Fractions of a second are omitted if not necessary. CURRENT_TIMESTAMP is the same as NOW().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
CURTIME
Syntax: CURTIME()
Returns the current time as a value in 'HH:MM:SS' format. CURRENT_TIME is identical to CURRENT_TIME.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DATEADD
Syntax: DATEADD(datepart, value, dateexpr)
Adds a specified datepart to a date.
| Datepart | Abbreviation |
|---|---|
| year | yy |
| quarter | |
| month | mm |
| dayofyear | dy |
| day | dd |
| week | wk |
| weekday | dw |
| hour | hh |
| minute | mi, n |
| second | ss, s |
| millisecond | ms |
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DATEDIFF
Syntax: DATEDIFF(datepart, value, dateexpr)
Returns the difference between two dates on a given datepart.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DAY
Syntax: DAY(dateexpr)
Returns the day of the month in the range 1 to 31 of dateexpr. Equivalent to DAYOFMONTH().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DAYNAME
Syntax: DAYNAME(dateexpr)
Returns the name of the weekday for dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DAYOFMONTH
Syntax: DAYOFMONTH(dateexpr)
Returns the day of the month in the range 1 to 31 of dateexpr. Equivalent to DAY().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DAYOFWEEK
Syntax: DAYOFWEEK(dateexpr)
Returns the weekday index for date (1 = Sunday, 2 = Monday .. 7 = Saturday) of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
DAYOFYEAR
Syntax: DAYOFYEAR(dateexpr)
Returns the day of the year in the range of 1 to 366 of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
GETDATE
Syntax: GETDATE()
Returns the current date as a value in 'YYYY-MM-DD' format. GETDATE() is identical to CURDATE().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
HOUR
Syntax: HOUR(dateexpr)
Returns the hour for time of dateexpr. The range of the return value is 0 to 23 for time-of-day values.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
MINUTE
Syntax: MINUTE(dateexpr)
Returns the minute for time, in the range 0 to 59.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
MONTH
Syntax: MONTH(dateexpr)
Returns the month for date, in the range 1 to 12 for January to December, or 0 for dates such as '0000-00-00' or '2008-00-00' that have a zero month part.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
MONTHNAME
Syntax: MONTHNAME(dateexpr)
Returns the name of the month of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
NOW
Syntax: NOW()
Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS.uuuuuu' format. Fractions of a second are omitted if not necessary. NOW() is the same as CURRENT_TIMESTAMP.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
QUARTER
Syntax: QUARTER(dateexpr)
Returns the quarter for date in the range 1 to 4 for dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
SECOND
Syntax: SECOND(dateexpr)
Returns the second in the range of 0 to 59 of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
SYSDATE
Syntax: SYSDATE
Returns the current date as a value in 'YYYY-MM-DD' format. SYSDATE is identical to CURDATE().
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
TO_CHAR
Syntax: TO_CHAR(date, formatexpr)
Converts a date into a string with a given format.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
TO_DATE
Syntax: TO_DATE(datestr, formatexpr)
Given a date format specified in Java syntax, returns a date or NULL if conversion fails.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
WEEK
Syntax: WEEK(dateexpr)
Returns the week number from 1 to 52 of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |
YEAR
Syntax: YEAR(dateexpr)
Returns the year of dateexpr.
| SQL Server | Access | Oracle | PostgreSQL | MySQL | Sybase | HSQLDB |
|---|---|---|---|---|---|---|
| | | | | | |