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.
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

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.

DatepartAbbreviation
yearyy
quarterqq
monthmm
dayofyeardy
daydd
weekwk
weekdaydw
hourhh
minutemi, n
secondss, s
millisecondms

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