All Products
Search
Document Center

Date and time functions and operators

Last Updated: Jan 15, 2019

Time zone conversion

AT TIME ZONE sets the time zone of the specified timestamp.

  1. SELECT timestamp 2012-10-31 01:00 UTC’; 2012-10-31 01:00:00.000 UTC
  2. SELECT timestamp 2012-10-31 01:00 UTC AT TIME ZONE America/Los_Angeles’; 2012-10-30 18:00:00.000 America/Los_Angeles

Date and time functions

current_date → date

Returns the current date.

current_time → time with time zone

Returns the current time.

current_timestamp → timestamp with time zone

Returns the current timestamp.

current_timezone() → varchar

Returns the current time zone in IANA format (for example, Asia/Shanghai), or as a fixed UTC offset (for example, +08:00).

from_iso8601_timestamp(string) → timestamp with time zone

Parses an ISO 8601 format string into a timestamp with time zone.

from_iso8601_date(string) → date

Parses an ISO 8601 format string into a date.

from_unixtime(unixtime) → timestamp

Returns a UNIX timestamp unixtime.

from_unixtime(unixtime, string) → timestamp with time zone

Returns a UNIX timestamp unixtime that uses a string as the time zone.

from_unixtime(unixtime, hours, minutes) → timestamp with time zone

Returns a UNIX timestamp unixtime with a time zone offset that uses hours and minutes.

localtime → time

Returns the current time based on the session.

localtimestamp → timestamp

Returns the current timestamp based on the session.

now() → timestamp with time zone

The alias for current_timestamp.

to_iso8601(x) → varchar

Formats x into an ISO 8601 string. x can be a date, timestamp, or timestamp with time zone.

to_unixtime(timestamp) → double

Returns a UNIX timestamp.

Note:

The following SQL-standard functions do not require parenthesis:

  • current_date
  • current_time
  • current_timestamp
  • localtime
  • localtimestamp

Truncation functions

date_trunc(unit, x) → [same as input]Returns x truncated as a specified unit.

The following examples use the timestamp 2001-08-22 03:04:05.321 as input.

The date_trunc function supports the following units:

Unit Sample truncated value
second 2001-08-22 03:04:05.000
minute 2001-08-22 03:04:00.000
hour 2001-08-22 03:00:00.000
day 2001-08-22 00:00:00.000
week 2001-08-20 00:00:00.000
month 2001-08-01 00:00:00.000
quarter 2001-07-01 00:00:00.000
year 2001-01-01 00:00:00.000

Interval functions

The interval functions support the following units:

Unit Description
millisecond Milliseconds
second Seconds
minute Minutes
hour Hours
day Days
week Weeks
month Months
quarter Quarters of a year
year Years

date_add(unit, value, timestamp) → [same as input]

Adds an interval unit value to the timestamp. Subtraction can be performed with negative values.

date_diff(unit, timestamp1, timestamp2) → bigint

Returns timestamp2 minus timestamp1 in units.

MySQL date functions

The functions in this section use MySQL date_parse and str_to_date function compatible format strings. The following table describes the format specifiers:

Specifier Description
%a Abridged weekday name (Sun .. Sat)
%b Abridged month name (Jan .. Dec)
%c Month, numeric (0 .. 12)
%D Day of the month with English suffix (0th, 1st, 2nd, 3rd, …)
%d Day of the month, numeric (00 .. 31)
%e Day of the month, numeric (0 .. 31)
%f Fraction of a second (6 digits for printing: 000000 .. 999000; 1 - 9 digits for parsing: 0 .. 999999999)
%H Hour (00 .. 23)
%h Hour (01 .. 12)
%I Hour (01 .. 12)
%i Minutes, numeric (00 .. 59)
%j Day of year (001 .. 366)
%k Hour (0 .. 23)
%l Hour (1 .. 12)
%M Month name (January .. December)
%m Month, numeric (00 .. 12)
%p AM or PM
%r Time, 12-hour (hh:mm:ss followed by AM or PM)
%S Seconds (00 .. 59)
%s Seconds (00 .. 59)
%T Time, 24-hour (hh:mm:ss)
%U Week (00 .. 53), when Sunday is the first day of the week
%u Week (00 .. 53), when Monday is the first day of the week
%V Week (01 .. 53), when Sunday is the first day of the week; used with %X
%v Week (01 .. 53), when Monday is the first day of the week; used with %x
%W Weekday name (Sunday .. Saturday)
%w Day of the week (0 .. 6), when Sunday is the first day of the week
%X Year for the week when Sunday is the first day of the week, numeric, four digits; used with %V
%x Year for the week, when Monday is the first day of the week, numeric, four digits; used with %v
%Y Year, numeric, four digits
%y Year, numeric (two digits)
%% A literal % character
%x x, for any x not listed above

Notes:

  • Timestamp is truncated to milliseconds.

  • When parsing, a two-digit year format assumes range of 1970 to 2069, so “70” results in 1970 but “69” will produce 2069.

  • These specifiers are not supported: %D %U %u %V %w %X

date_format(timestamp, format) → varcharFormats timestamp as a string.

date_parse(string, format) → timestampParses string into a timestamp with format.

Java date functions

The following Java date functions use a format string that is compatible with Java’s SimpleDateFormat pattern format.

format_datetime(timestamp, format) → varchar

Formats timestamp as a string.

parse_datetime(string, format) → timestamp with time zone

Parses string into a timestamp with time zone with format.

Extraction functions

The extract functions support the following fields:

Field Description
YEAR year()
QUARTER quarter()
MONTH month()
WEEK week()
DAY day()
DAY_OF_MONTH day()
DAY_OF_WEEK day_of_week()
DOW day_of_week()
DAY_OF_YEAR day_of_year()
DOY day_of_year()
YEAR_OF_WEEK year_of_week()
YOW year_of_week()
HOUR hour()
MINUTE minute()
SECOND second()
TIMEZONE_HOUR timezone_hour()
TIMEZONE_MINUTE timezone_minute()

The types supported by extract functions vary according to the extracted field. All date and time types are supported by most fields.

extract(field FROM x) → bigint

Returns field from x.

Note:

This SQL-standard function uses special syntax for specifying arguments.

Convenience extraction functions

day(x) → bigint

Returns the day (day of the month) from the date specified by x.

day_of_month(x) → bigint

The alias for day.

day_of_week(x) → bigint

Returns the ISO day of the week as specified by x. The value ranges from 1 (Monday) to 7 (Sunday).

day_of_year(x) → bigint

Returns the day of the year as specified by x. The value ranges from 1 to 366.

dow(x) → bigint

The alias for day_of_week.

doy(x) → bigint

The alias for day_of_year.

hour(x) → bigint

Returns the hour of the day from the specified date by x. The value ranges from 0 to 23.

minute(x) → bigint

Returns the minute of the hour as specified by x.

month(x) → bigint

Returns the month as specified by x .

quarter(x) → bigint

Returns the quarter of the year as specified by x. The value range is from 1 to 4.

second(x) → bigint

Returns the second of the minute as specified by x.

timezone_hour(timestamp) → bigint

Returns the time zone offset hour of the timestamp.

timezone_minute(timestamp) → bigint

Returns the time zone offset minute of the timestamp.

week(x) → bigint

Returns the ISO week of the year as specified by x. The value ranges from 1 to 53.

week_of_year(x) → bigint

The alias for week.

year(x) → bigint

Returns the year as specified by x.

year_of_week(x) → bigint

Returns the year of the ISO week as specified by x.

yow(x) → bigint

The alias for year_of_week.

Teradata date and time functions

The functions in this section use a format string that is compatible with the Teradata datetime functions. The following table is based on the Teradata reference manual and describes the supported format specifiers:

Format Description
- / , . ; : Punctuation characters are ignored
dd Day of month (1 to 31)
hh Hour of day (1 to 12)
hh24 Hour of day (0 to 23)
mi Minute (0 to 59)
mm Month (01 to 12)
ss Second (0 to 59)
yyyy 4-digit year
yy 2-digit year
Note

Case sensitivity is currently not supported. All specifiers must be lowercase.

to_char(timestamp, format) → varchar

Formats timestamp as a string.

to_timestamp(string, format) → timestamp

Parses string into a timestamp with format.

to_date(string, format) → date

Parses string into a date with format.

Compatible MySQL date and time function

ADDDATE

Adds a time value (interval) to a date value. When invoked with the second argument INTERVAL form, ADDDATE() is a synonym for DATE_ADD().Syntax:

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

Example:

  1. SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
  2. SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
  3. SELECT ADDDATE('2008-01-02', 31);

ADDTIME

Returns the sum of expr2 to expr1. expr1 is a time or datetime expression, and expr2 is a time expression.

Syntax:

ADDTIME(expr1,expr2)

Example:

  1. SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
  2. SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

CURDATE

Returns the current date in ‘YYYY-MM-DD’ format. CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

Syntax:

CURDATE()

Example:

  1. SELECT CURDATE();
  2. SELECT CURRENT_DATE();
  3. SELECT CURRENT_DATE;

SYSDATE

Returns the current date and time values in the format ‘YYYY-MM-DD HH:MM:SS’.

Syntax:

SYSDATE()

Example:

  1. SELECT SYSDATE();

CURRENT_DATE

The synonym for CURDATE().

Syntax:

CURRENT_DATE, CURRENT_DATE()

Example:

  1. SELECT CURRENT_DATE;
  2. SELECT CURRENT_DATE();

CURRENT_TIME

The synonym for CURTIME().

Syntax:

CURRENT_TIME, CURRENT_TIME()

Example:

  1. SELECT CURRENT_TIME;
  2. SELECT CURRENT_TIME();

CURTIME

Returns the current time value in ‘HH.MM.SS’ format. CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

Syntax:

CURTIME()

Example:

  1. SELECT CURTIME();
  2. SELECT CURRENT_TIME();
  3. SELECT CURRENT_TIME;

Yearmonth

Queries the specified column’s year and month.

Example:

YEARMONTH(‘20140602’)=201406

DATE

Extracts the date part of date or datetime expression expr.

Syntax:

DATE(expr)

Example:

  1. SELECT DATE('2003-12-31 01:02:03')

DATEDIFF

DATEDIFF() returns the difference between two dates expr1 − expr2 expressed in the number of days. expr1 and expr2 are date or date-and-time expressions. Only date values are used in the calculation.

Syntax:

DATEDIFF(expr1,expr2)

Example:

  1. SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
  2. SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');

DATE_FORMAT

Formats the date value based on the format string.

Syntax:

DATE_FORMAT(expr1,expr2)

Example:

  1. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
  2. SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
  3. SELECT DATE_FORMAT('1997-10-04 22:23:00', '%d');
  4. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');
  5. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%y-%m-%d');
  6. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %T');
  7. SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %r');

DAY

DAY() is a synonym for DAYOFMONTH().

Syntax:

DAY(date)

Example:

  1. SELECT DAY('2007-02-03');
  2. SELECT DAYOFMONTH('2007-02-03');

DAYNAME

Returns the weekday name for a date.

Syntax:

DAYNAME(date)

Example:

  1. SELECT DAYNAME('2007-02-03');

DAYOFWEEK

Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Syntax:

DAYOFWEEK(date)

Example:

  1. SELECT DAYOFWEEK('2007-02-03');

DAYOFYEAR

Returns the day of the year for date.

Syntax:

DAYOFYEAR(date)

Example:

  1. SELECT DAYOFYEAR('2007-02-03');

EXTRACT

The EXTRACT function uses the same types of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts values from the date instead of performing date arithmetic.

Syntax:

EXTRACT(unit FROM date)

Examples:

  1. SELECT EXTRACT(YEAR FROM '2009-07-02');
  2. SELECT EXTRACT(MONTH FROM '2009-07-02');
  3. SELECT EXTRACT(DAY FROM '2009-07-02');
  4. SELECT EXTRACT(HOUR FROM '2003-01-02 10:30:00');
  5. SELECT EXTRACT(MINUTE FROM '2003-01-02 10:30:00');
  6. SELECT EXTRACT(SECOND FROM '2003-01-02 10:30:00');

FROM_DAYS

Returns a DATE value when a given day number is N.

Syntax:

FROM_DAYS(N)

Example:

  1. SELECT FROM_DAYS(730669);

FROM_UNIXTIME

Returns a representation of the unix_timestamp argument in ‘YYYY-MM-DD HH:MM:SS’ format.

Syntax:

FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

Example:

  1. SELECT FROM_UNIXTIME(1447430881);
  2. SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd');

HOUR

Returns the hour of a time value.

Syntax:

HOUR(time)

Example:

  1. SELECT HOUR('10:05:03');
  2. SELECT HOUR('272:59:59');

LAST_DAY

Returns the corresponding value for last day of the month for a date or datetime value.

Syntax:

LAST_DAY(date)

Example:

  1. SELECT LAST_DAY('2003-02-05');
  2. SELECT LAST_DAY('2004-01-01 01:01:01');

LOCALTIME

LOCALTIME and LOCALTIME() are synonyms for NOW().

Syntax:

LOCALTIME , LOCALTIME()

Example:

  1. SELECT LOCALTIME;
  2. SELECT LOCALTIME();

NOW

A synonym for LOCALTIME and LOCALTIME().

Syntax:

NOW()

Example:

  1. SELECT NOW();

LOCALTIME

LOCALTIMESTAMP and LOCALTIMESTAMP() are synonyms for NOW().

Syntax:

LOCALTIME , LOCALTIME()

Example:

  1. SELECT LOCALTIMESTAMP;
  2. SELECT LOCALTIMESTAMP();

MAKETIME

Returns a time value calculated from the hour, minute, and second arguments.

Syntax:

MAKETIME(hour,minute,second)

Example:

  1. SELECT MAKETIME(12,15,30);

MINUTE

Returns the minute of a time value.

Syntax:

MINUTE(time)

Example:

  1. SELECT MINUTE('2008-02-03 10:05:03');

MONTH

Returns the month for a date.

Syntax:

MONTH(date)

Example:

  1. SELECT MONTH('2008-02-03');

MONTHNAME

Returns the full month name for a date.

Syntax:

MONTHNAME(date)

Example:

  1. SELECT MONTHNAME('2008-02-03');

NOW

Returns the current date and time in ‘YYYY-MM-DD HH:MM:SS’ format.

Syntax:

NOW()

Example:

  1. SELECT NOW();

PERIOD_ADD (P, N)

Adds N months to period P (in YYMM or YYYYMM format). Returns a value in the format YYYYMM.

Syntax:

PERIOD_ADD(P,N)

Example:

  1. SELECT PERIOD_ADD(200801,2);

PERIOD_DIFF (P1, P2)

Returns the number of months between periods P1 and P2.

Syntax:

PERIOD_DIFF(P1,P2)

Example:

  1. SELECT PERIOD_DIFF(200802,200703);

QUARTER

Returns the quarter of the year for a date, in the range 1 to 4.

Syntax:

QUARTER(date)

Example:

  1. SELECT QUARTER('2008-04-01');

SECOND (time)

Returns the seconds value, in the range 0 to 59.

Syntax:

SECOND(time)

Example:

  1. SELECT SECOND('10:05:03');

SEC_TO_TIME (seconds)

Returns the seconds argument as a time value that is converted to hours, minutes, and seconds..

Syntax:

SEC_TO_TIME(seconds)

Example:

  1. SELECT SEC_TO_TIME(2378);

STR_TO_DATE (str, format)

This is inverse of the DATE_FORMAT() function. It takes a string str and formats it into a string.

Syntax:

STR_TO_DATE(str,format)

Example:

  1. SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

SUBDATE

When invoked with the second argument INTERVAL form , SUBDATE() is a synonym for DATE_SUB().

Syntax:

SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

Example:

  1. SELECT DATE_SUB('2008-01-02', INTERVAL 31 DAY);
  2. SELECT SUBDATE('2008-01-02', INTERVAL 31 DAY);
  3. SELECT SUBDATE('2008-01-02 12:00:00', 31);

SUBTIME

Returns the value of expr1-expr2 in the same format as expr 1.

Syntax:

SUBTIME(expr1,expr2)

Example:

  1. SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
  2. SELECT SUBTIME('01:00:00.999999', '02:00:00.999998');

STR_TO_DATE

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time, but will return either a DATE or TIME value if the format string contains only the date or time.

Syntax:

STR_TO_DATE(str,format)

Example:

  1. SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y');

TIME

Extracts the time of the time or datetime expression expr, and returns it as a string.

Syntax:

TIME(expr)

Example:

  1. SELECT TIME('2003-12-31 01:02:03');

TIMESTAMP

When using a single argument, the function returns the date or datetime expression expr as a datetime value. When using two arguments, the function adds the time expression expr2 to the date or datetime expression expr1 and returns the result as a datetime value.

Syntax:

TIMESTAMP(expr) , TIMESTAMP(expr1,expr2)

Example:

  1. SELECT TIMESTAMP('2003-12-31');
  2. SELECT TIMESTAMP('2003-12-31 12:00:00','12:00:00');

TIMESTAMPADD

Adds the integer expression interval to the date or datetime expression datetime_expr.

Syntax:

TIMESTAMPADD(unit,interval,datetime_expr)

Example:

  1. SELECT TIMESTAMPADD(WEEK,1,'2003-01-02');
  2. SELECT TIMESTAMPADD(YEAR,1,'2003-01-02');
  3. SELECT TIMESTAMPADD(MONTH,1,'2003-01-02');
  4. SELECT TIMESTAMPADD(DAY,1,'2003-01-02');

TIMESTAMPDIFF

Returns datetime_expr2 − datetime_expr1 that expresses datetime_expr1 and datetime_expr2 as date or datetime expressions.Syntax:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Example:

  1. SELECT TIMESTAMPDIFF(MONTH,'2003-02-01','2003-05-01');
  2. SELECT TIMESTAMPDIFF(YEAR,'2002-05-01','2001-01-01');
  3. SELECT TIMESTAMPDIFF(MINUTE,'2003-02-01','2003-05-01 12:05:55');

TIME_TO_SEC

Returns the time argument in seconds.

Syntax:

TIME_TO_SEC(time)

Example:

  1. SELECT TIME_TO_SEC('22:23:00');
  2. SELECT TIME_TO_SEC('00:39:38');

TO_DAYS

When given a date expr, returns a day number (the number of days since year 0).Returns a day number (the number of days since year 0) for a given date expr.

Syntax:

TO_DAYS(date)

Example:

  1. SELECT TO_DAYS('2007-10-07');
  2. SELECT TO_DAYS('2008-10-07');
  3. SELECT TO_DAYS('0000-00-00');

TO_SECONDS

Given a date or datetime expr, returns the value for the number of seconds since the year 0. Null is returned if the expr is an invalid date or datetime value.

Syntax:

TO_SECONDS(date)

Example:

  1. SELECT TO_SECONDS('2009-11-29');
  2. SELECT TO_SECONDS('2009-11-29 13:43:32');
  3. SELECT TO_SECONDS('0000-00-00');

UNIX_TIMESTAMP

If called with no argument, returns a Unix timestamp (in seconds since ‘1970-01-01 00:00:00’ UTC).

Syntax:

UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)

Example:

  1. SELECT UNIX_TIMESTAMP();
  2. SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
  3. SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
  4. SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');

UTC_DATE

Returns the current UTC date as in ‘YYYY-MM-DD’ format.

Syntax:

UTC_DATE()

Example:

  1. SELECT UTC_DATE()

UTC_TIME

Returns the current UTC time as a value in ‘HH:MM:SS’ format.

Syntax:

UTC_TIME()

Example:

  1. SELECT UTC_TIME()

UTC_TIMESTAMP

Returns the current UTC date and time in ‘YYYY-MM-DD HH:MM:SS’ format.

Syntax:

UTC_TIMESTAMP()

Example:

  1. SELECT UTC_TIMESTAMP()

WEEK

The WEEK function returns the week number for a date. The two-argument form of WEEK() allows you to specify if the week starts on Sunday or Monday and if the return value range is from 0 to 53, or from 1 to 53.

Syntax:

WEEK(date[,mode])

Example:

  1. SELECT WEEK('2008-02-20');
  2. SELECT WEEK('2008-02-20',0);
  3. SELECT WEEK('2008-02-20',1);

WEEKDAY

Returns the weekday index of a date.

Syntax:

WEEKDAY(date)

Example:

  1. SELECT WEEKDAY('2008-02-03 22:23:00');
  2. SELECT WEEKDAY('2007-11-06');

WEEKOFYEAR

Returns the calendar week of the date as a number in the range from 1 to 53.

Syntax:

WEEKOFYEAR(date)

Example:

  1. SELECT WEEKOFYEAR('2008-02-20');

YEAR

Returns the year for date, in the range 1000 to 9999, or 0 for the “zero” date.

Syntax:

YEAR(date)

Example:

  1. SELECT YEAR('1987-01-01');

YEARWEEK

Returns year and week for a date. The year in the result may be different from the first and last week of the year of the date argument.

Syntax:

YEARWEEK(date) , YEARWEEK(date,mode)

Example:

  1. SELECT YEARWEEK('1987-01-01');
  2. SELECT YEARWEEK('1987-01-01',1);

TIMEDIFF

Returns expr1 − expr2 expressed as a time value. expr1 and expr2 must be the same time or date-and-time expressions datatypes .

Syntax:

TIMEDIFF(expr1,expr2)

Example:

  1. SELECT TIMEDIFF('2008-12-31 23:59:50', '2008-12-31 23:59:59');
  2. SELECT TIMEDIFF('2008-12-30 23:59:59', '2008-12-31 23:59:59');
  3. SELECT TIMEDIFF('2008-12-30 11:59:59', '2008-12-31 23:59:59');
  4. SELECT TIMEDIFF('2008-12-30 11:50:59', '2008-12-31 23:59:59');
  5. SELECT TIMEDIFF('2008-12-30 11:50:50', '2008-12-31 23:59:59');

CONVERT_TZ

Converts a datetime value dt from the time zone given by from_tz, to the time zone given by to_tz, and returns the resulting value.

Syntax:

CONVERT_TZ(dt,from_tz,to_tz)

Example:

  1. SELECT CONVERT_TZ('2004-01-01 12:00:00','GMT','MET');

TIME_FORMAT

This is applied similarly to the DATE_FORMAT() function, but the format string may contain format specifiers solely for hours, minutes, seconds, and microseconds arguments. Other specifiers return NULL value or 0.

Syntax:

TIME_FORMAT(time,format)

Example:

  1. SELECT TIME_FORMAT('10:00:00', '%H %k %h %I %l');

Compatible Oracle date and time functions

ADD_MONTHS

Returns the date date and integer months.

Syntax:

ADD_MONTHS(date, integer)

Example:

  1. ADD_MONTHS('2010-10-10',1)

CURRENT_DATE

Returns the current date in the session time zone, as a value in the Gregorian calendar of datatype DATE.

Syntax:

CURRENT_DATE

Example:

  1. SELECT CURRENT_DATE;

CURRENT_TIMESTAMP

Returns the current date and time in the session time zone, in a datatype value TIMESTAMP WITH TIME ZONE.

Syntax:

CURRENT_TIMESTAMP

Example:

  1. SELECT CURRENT_TIMESTAMP;

DBTIMEZONE

Returns the database time zone value.

Syntax:

DBTIMEZONE()

Example:

  1. SELECT DBTIMEZONE();

EXTRACT

Extracts and returns the value of a specified datetime field from a datetime or interval value expression.

Syntax:

EXTRACT(unit FROM datetime)

Example:

  1. SELECT EXTRACT(YEAR FROM '2001-01-01');
  2. SELECT EXTRACT(MONTH FROM '2001-01-01');
  3. SELECT EXTRACT(DAY FROM '2001-01-01');
  4. SELECT EXTRACT(HOUR FROM '2001-01-01 19:10:11');
  5. SELECT EXTRACT(MINUTE FROM '2001-01-01 19:10:11');
  6. SELECT EXTRACT(SECOND FROM '2001-01-01 19:10:11');

LAST_DAY

Returns the date of the last day of the month.

Syntax:

LAST_DAY

Example:

  1. LAST_DAY('2001-01-01');

LOCALTIMESTAMP

Returns the current date and time in the session time zone as a TIMESTAMP datatype value.

Syntax:

LOCALTIMESTAMP()

Example:

  1. SELECT LOCALTIMESTAMP();

MONTH_BETWEEN

Returns the number of months between dates (date1, date2).

Syntax:

MONTH_BETWEEN(date1, date2)

Example:

  1. SELECT MONTH_BETWEEN('2017-03-03', '2017-07-07');
  2. SELECT MONTH_BETWEEN('2017-04-03', '2017-07-07');

NEXT_DAY

Returns the date of the first weekday named by char that is later than the date date.

Syntax:

NEXT_DAY(date, char)

Example:

  1. SELECT NEXT_DAY('2010-10-10','TUESDAY');
  2. SELECT NEXT_DAY('2010-10-10','TUE');

ROUND

Returns date rounded to the unit specified by the format model fmt. The value returned is always a datatype DATE, even when a different datatype for date is specified.

Syntax:

ROUND(date, fmt)

Example:

  1. SELECT ROUND(TIMESTAMP '2010-08-21', 'YY');
  2. SELECT ROUND(TIMESTAMP '2010-08-21', 'MM');
  3. SELECT ROUND(TIMESTAMP '2010-08-21', 'q');
  4. SELECT ROUND(TIMESTAMP '2010-08-21', 'D');
  5. SELECT ROUND(TIMESTAMP '2010-08-21 19:00:00', 'DD');

SESSIONTIMEZONE

Returns the time zone of the current session.

Syntax:

SESSIONTIMEZONE()

Example:

  1. SELECT SESSIONTIMEZONE();

SYSDATE

Returns the current date and time taken from the operating system on which the database resides.

Syntax:

SYSDATE()

Example:

  1. SELECT SYSDATE();

TO_CHAR

Converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE to a value of VARCHAR2 datatype in the format specified by the date format fmt.

Syntax:

TO_CHAR(datetime, fmt)

Example:

  1. SELECT TO_CHAR('2013-05-17 23:35:10', '%Y-%m-%d %H:%i:%s');
  2. SELECT TO_CHAR('2013-05-17 00:35:10', '%Y-%m-%d %H:%i:%s');
  3. SELECT TO_CHAR('2013-05-17 12:35:10', '%Y-%m-%d %h:%i:%s %p');

TRUNC

Returns date with the time part of the day truncated to the unit specified by the format model fmt.

Syntax:

TRUNC(date, fmt)

Examples:

  1. SELECT TRUNC(TIMESTAMP '2010-08-21', 'YY');
  2. SELECT TRUNC(TIMESTAMP '2010-08-21', 'MM');
  3. SELECT TRUNC(TIMESTAMP '2010-08-21', 'q');
  4. SELECT TRUNC(TIMESTAMP '2010-08-21 19:00:00', 'DD');

TO_DATE

Converts the char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.

Syntax:

TO_DATE(char, fmt)

Example:

  1. SELECT TO_DATE('2013-05', '%Y-%m');
  2. SELECT TO_DATE('2013-05-17 12:35:10', '%Y-%m-%d %h:%i:%s');

NEW_TIME

Returns the date and time in time zone timezone2, if date and time in time zone timezone1 are dates.

Syntax:

NEW_TIME(date, timezone1, timezone2)

Example:

  1. SELECT NEW_TIME('2004-01-01 12:00:00','GMT','MET');
  2. SELECT NEW_TIME('2004-01-01 12:00:00.123','GMT','MET');