Data Lake Analytics - Deprecated:Date and time functions

Time zone conversion

The AT TIME ZONE operator is used to set the time zone of a timestamp.

SELECT timestamp '2012-10-31 01:00 UTC'; 2012-10-31 01:00:00.000 UTC

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 as of the start of the query.

current_time -> time with time zone

Returns the current time with a time zone as of the start of the query.

current_timestamp -> timestamp with time zone

Returns the current timestamp with a time zone as of the start of the query.

current_timezone → varchar

Returns the current time zone in the format defined by IANA (example: America / Los_Angeles) or based on a fixed offset from UTC (example: +08:35).

from_iso8601_timestamp(string) → timestamp with time zone

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

from_iso8601_date(string) → date

Parses an ISO 8601 formatted date string into a date.

from_unixtime(unixtime) → timestamp

Returns a UNIX timestamp.

from_unixtime(unixtime, string) → timestamp with format

Returns a UNIX timestamp in a specified format.

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

Returns a UNIX timestamp with a time zone. Hours and minutes are used for the time zone offset.

localtime -> time

Returns the current time with a time zone as of the start of the query.

localtimestamp -> timestamp

Returns the current timestamp with a time zone as of the start of the query.

now() → timestamp with time zone

This is an alias for current_timestamp.

to_iso8601(x) → varchar

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

to_unixtime(timestamp) → double

Returns a UNIX timestamp.

Usage notes

The following SQL-standard functions do not use parentheses ():

• current_date
• current_time
• current_timestamp
• localtime
• localtimestamp

Truncation function

The following table describes the units supported by the date_trunc function.

In the preceding examples, the timestamp 2001-08-22 03:04:05.321 is used as the input.

date_trunc(unit, x) → [same as input]

Returns x truncated to unit.

Interval functions

The functions in this section support the interval units that are described in the following table.

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

Adds an interval value of the unit type to a timestamp. Subtraction can be performed by using a negative value.

date_diff(unit, timestamp1, timestamp2) → bigint

Returns the value of timestamp1 subtracted from timestamp2 in terms of unit.

MySQL date functions

The functions in this section use a formatted string that is compatible with the MySQL date_parse and str_to_date functions. The following table describes the format specifiers based on the MySQL manual.

• A timestamp is truncated to milliseconds.
• Assume that the format of a two-digit year is 1970.2069. 70 indicates the year 1970, and 69 indicates the year 2069.
• The following specifiers are not supported: %D %U %u %V %w %X.

date_format(timestamp, format) → varchar: formats a timestamp as a string in a specified format.

date_parse(string, format) → timestamp: parses a string into a timestamp in a specified format.

Java date functions

The functions in this section use a formatted string that is compatible with the SimpleDateFormat pattern format of Java.

format_datetime(timestamp, format) → varchar

Formats a timestamp as a string in a specified format.

parse_datetime(string, format) → timestamp with time zone

Parses a string into a timestamp with a time zone in a specified format.

Extraction function

The extract() function supports the fields that are described in the following table.

The data types supported by the extract() function vary based on the field to be extracted. Most fields support date and time types.

extract(field FROM x) → bigint

Returns a field from x.

Usage notes

• This SQL-standard function uses specific syntax to specify parameters.

Convenience extraction functions

day(x) → bigint

Returns the day of the month from x.

day_of_month(x) → bigint

This is an alias for day(x).

day_of_week(x) → bigint

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

day_of_year(x) → bigint

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

dow(x) → bigint

This is an alias for day_of_week().

doy(x) → bigint

This is an alias for day_of_year().

hour(x) → bigint

Returns the hour of the day from x. The value ranges from 1 to 23.

minute(x) → bigint

Returns the minute of the hour from x.

month(x) → bigint

Returns the month of the year from x.

quarter(x) → bigint

Returns the quarter of the year from x. The value ranges from 1 to 4.

second(x) → bigint

Returns the second of the minute from x.

timezone_hour(timestamp) → bigint

Returns the hour-based time zone offset from a specified timestamp.

timezone_minute(timestamp) → bigint

Returns the minute-based time zone offset from a specified timestamp.

week(x) → bigint

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

week_of_year(x) → bigint

This is an alias for week().

year(x) → bigint

Returns the year from x.

year_of_week(x) → bigint

Returns the year of the ISO week from x.

yow(x) → bigint

This is an alias for year_of_week().

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

to_char(timestamp, format) → varchar

Returns a date string in a specified format.

to_timestamp(string, format) → timestamp

Parses a string into a timestamp in a specified format.

to_date(string, format) → date

Parses a string into a date in a specified format.

MySQL date and time functions

This section describes MySQL date and time functions supported by DLA.

ADDDATE

Add time values (intervals) to a date value. When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD().

Syntax:

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

Example:

SELECT DATE_ADD('2008-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('2008-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('2008-01-02', 31);

ADDTIME

ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

Syntax:

ADDTIME(expr1,expr2)

Example:

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

CURDATE

Returns the current date as a value in the YYYY-MM-DD format. CURRENT_DATE and CURRENT_DATE() are synonyms for CURDATE().

Syntax:

CURDATE()

Example:

SELECT CURDATE();
SELECT CURRENT_DATE();
SELECT CURRENT_DATE;

SYSDATE

Returns the current date and time as a value in the YYYY-MM-DD HH:MM:SS format.

Syntax:

SYSDATE()

Example:

SELECT SYSDATE();

CURRENT_DATE

Synonyms for CURDATE()

Syntax:

CURRENT_DATE, CURRENT_DATE()

Example:

SELECT CURRENT_DATE;
SELECT CURRENT_DATE();

CURRENT_TIME

Synonyms for CURTIME()

Syntax:

CURRENT_TIME, CURRENT_TIME()

Example:

SELECT CURRENT_TIME;
SELECT CURRENT_TIME();

CURTIME

Returns the current date as a value in the HH.MM.SS format. CURRENT_TIME and CURRENT_TIME() are synonyms for CURTIME().

Syntax:

CURTIME()

Example:

SELECT CURTIME();
SELECT CURRENT_TIME();
SELECT CURRENT_TIME;

YEARMONTH

Queries the day and month of a specified column. Example: YEARMONTH('20140602')=201406.

DATE

Extracts the date part of the date or datetime expression expr.

Syntax:

DATE(expr)

Example:

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

DATEDIFF

Returns the value of expr2 subtracted from expr1 in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

Syntax:

DATEDIFF(expr1,expr2)

Example:

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

DATE_FORMAT

Formats the date value according to the format string.

Syntax:

DATE_FORMAT(expr1,expr2)

Example:

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%y-%m-%d');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %T');
SELECT DATE_FORMAT('2009-10-04 22:23:00', '%Y-%m-%d %r');

DAY

DAY() is a synonym for DAYOFMONTH().

Syntax:

DAY(date)

Example:

SELECT DAY('2007-02-03');
SELECT DAYOFMONTH('2007-02-03');

DAYNAME

Returns the name of the weekday for date.

Syntax:

DAYNAME(date)

Example:

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

DAYOFWEEK

Returns the weekday index for date.

Syntax:

DAYOFWEEK(date)

Example:

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

DAYOFYEAR

Returns the day of the year for date, in the range 1 to 366.

Syntax:

DAYOFYEAR(date)

Example:

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

EXTRACT

The EXTRACT() function uses the same kinds of unit specifiers as DATE_ADD() or DATE_SUB(), but extracts parts from the date rather than performing date arithmetic.

Syntax:

EXTRACT(unit FROM date)

Example:

SELECT EXTRACT(YEAR FROM '2009-07-02');
SELECT EXTRACT(MONTH FROM '2009-07-02');
SELECT EXTRACT(DAY FROM '2009-07-02');
SELECT EXTRACT(HOUR FROM '2003-01-02 10:30:00');
SELECT EXTRACT(MINUTE FROM '2003-01-02 10:30:00');
SELECT EXTRACT(SECOND FROM '2003-01-02 10:30:00');

FROM_DAYS

Given a day number N, returns a DATE value.

Syntax:

FROM_DAYS(N)

Example:

SELECT FROM_DAYS(730669);

FROM_UNIXTIME

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

Syntax:

FROM_UNIXTIME(unix_timestamp) , FROM_UNIXTIME(unix_timestamp,format)

Example:

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

HOUR

Returns the hour for time. The range of the return value is 0 to 23 for time-of-day values.

Syntax:

HOUR(time)

Example:

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

LAST_DAY

Takes a date or datetime value and returns the corresponding value for the last day of the month.

Syntax:

LAST_DAY(date)

Example:

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

LOCALTIME

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

Syntax:

LOCALTIME , LOCALTIME()

Example:

SELECT LOCALTIME;
SELECT LOCALTIME();

NOW

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

Syntax:

NOW()

Example:

SELECT NOW();

LOCALTIME

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

Syntax:

LOCALTIME , LOCALTIME()

Example:

SELECT LOCALTIMESTAMP;
SELECT LOCALTIMESTAMP();

MAKETIME

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

Syntax:

MAKETIME(hour,minute,second)

Example:

SELECT MAKETIME(12,15,30);

MINUTE

Returns the minute for time, in the range 0 to 59.

Syntax:

MINUTE(time)

Example:

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

MONTH

Returns the month for date, in the range 1 to 12 for January to December.

Syntax:

MONTH(date)

Example:

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

MONTHNAME

Returns the full name of the month for date.

Syntax:

MONTHNAME(date)

Example:

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

NOW

Returns the current date and time as a value in the YYYY-MM-DD HH:MM:SS format.

Syntax:

NOW()

Example:

SELECT NOW();

PERIOD_ADD

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

Syntax:

PERIOD_ADD(P,N)

Example:

SELECT PERIOD_ADD(200801,2);

PERIOD_DIFF

Returns the number of months between periods P1 and P2.

Syntax:

PERIOD_DIFF(P1,P2)

Example:

SELECT PERIOD_DIFF(200802,200703);

QUARTER

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

Syntax:

QUARTER(date)

Example:

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

SECOND

Returns the second for time, in the range 0 to 59.

Syntax:

SECOND(time)

Example:

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

SEC_TO_TIME

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value.

Syntax:

SEC_TO_TIME(seconds)

Example:

SELECT SEC_TO_TIME(2378);

STR_TO_DATE

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

Syntax:

STR_TO_DATE(str,format)

Example:

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

SUBDATE

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

Syntax:

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

Example:

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

SUBTIME

Returns the value of expr2 subtracted from expr1 in the same format as expr1.

Syntax:

SUBTIME(expr1,expr2)

Example:

SELECT SUBTIME('2007-12-31 23:59:59.999999','1 1:1:1.000002');
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.

Syntax:

STR_TO_DATE(str,format)

Example:

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

TIME

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

Syntax:

TIME(expr)

Example:

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

TIMESTAMP

With a single argument, this function returns the date or datetime expression expr as a datetime value. With two arguments, it 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:

SELECT TIMESTAMP('2003-12-31');
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:

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

TIMESTAMPDIFF

Returns the value of datetime_expr1 subtracted from datetime_expr2. datetime_expr1 and datetime_expr2 are date or datetime expressions.

Syntax:

TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2)

Example:

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

TIME_TO_SEC

Returns the time argument, converted to seconds.

Syntax:

TIME_TO_SEC(time)

Example:

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

TO_DAYS

Given a date date, returns a day number (the number of days since year 0).

Syntax:

TO_DAYS(date)

Example:

SELECT TO_DAYS('2007-10-07');
SELECT TO_DAYS('2008-10-07');
SELECT TO_DAYS('0000-00-00');

TO_SECONDS

Given a date or datetime expr, returns the number of seconds since the year 0. If expr is not a valid date or datetime value, returns NULL.

Syntax:

TO_SECONDS(date)

Example:

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

UNIX_TIMESTAMP

Returns a UNIX timestamp representing the number of seconds that have elapsed since the epoch time January 1, 1970, 00:00:00 UTC if no argument is specified for calling.

Syntax:

UNIX_TIMESTAMP() , UNIX_TIMESTAMP(date)

Example:

SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19');
SELECT UNIX_TIMESTAMP('2015-11-13 10:20:19.012');
SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');

UTC_DATE

Returns the current UTC date as a value in the YYYY-MM-DD format.

Syntax:

UTC_DATE()

Example:

SELECT UTC_DATE()

UTC_TIME

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

Syntax:

UTC_TIME()

Example:

SELECT UTC_TIME()

UTC_TIMESTAMP

Returns the current UTC date and time as a value in the YYYY-MM-DD HH:MM:SS format.

Syntax:

UTC_TIMESTAMP()

Example:

SELECT UTC_TIMESTAMP()

WEEK

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

Syntax:

WEEK(date[,mode])

Example:

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

WEEKDAY

Returns the weekday index for a date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Syntax:

WEEKDAY(date)

Example:

SELECT WEEKDAY('2008-02-03 22:23:00');
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:

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

YEAR

Returns the year for a date, in the range of 1000 to 9999, or 0 for the zero date.

Syntax:

YEAR(date)

Example:

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

YEARWEEK

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

Syntax:

YEARWEEK(date) , YEARWEEK(date,mode)

Example:

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

TIMEDIFF

Returns the value of expr2 subtracted from expr1. expr1 and expr2 are date-and-time expressions and must be of the same type.

Syntax:

TIMEDIFF(expr1,expr2)

Example:

SELECT TIMEDIFF('2008-12-31 23:59:50', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 23:59:59', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 11:59:59', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 11:50:59', '2008-12-31 23:59:59');
SELECT TIMEDIFF('2008-12-30 11:50:50', '2008-12-31 23:59:59');

CONVERT_TZ

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:

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

TIME_FORMAT

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, seconds, and microseconds. Other specifiers produce a NULL value or 0.

Syntax:

TIME_FORMAT(time,format)

Example:

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

Oracle date and time functions

This section describes Oracle date and time functions supported by DLA.

ADD_MONTHS

ADD_MONTHS returns the date date plus integer months.

Syntax:

ADD_MONTHS(date, integer)

Example:

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

CURRENT_DATE

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

Syntax:

CURRENT_DATE

Example:

SELECT CURRENT_DATE;

CURRENT_TIMESTAMP

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

Syntax:

CURRENT_TIMESTAMP

Example:

SELECT CURRENT_TIMESTAMP;

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone.

Syntax:

DBTIMEZONE()

Example:

SELECT DBTIMEZONE();

EXTRACT

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

Syntax:

EXTRACT(unit FROM datetime)

Example:

SELECT EXTRACT(YEAR FROM '2001-01-01');
SELECT EXTRACT(MONTH FROM '2001-01-01');
SELECT EXTRACT(DAY FROM '2001-01-01');
SELECT EXTRACT(HOUR FROM '2001-01-01 19:10:11');
SELECT EXTRACT(MINUTE FROM '2001-01-01 19:10:11');
SELECT EXTRACT(SECOND FROM '2001-01-01 19:10:11');

LAST_DAY

LAST_DAY returns the date of the last day of the month that contains date.

Syntax:

LAST_DAY

Example:

LAST_DAY('2001-01-01');

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP.

Syntax:

LOCALTIMESTAMP()

Example:

SELECT LOCALTIMESTAMP();

MONTH_BETWEEN

MONTHS_BETWEEN returns number of months between dates date1 and date2.

Syntax:

MONTH_BETWEEN(date1, date2)

Example:

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

NEXT_DAY

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:

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

ROUND

ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date.

Syntax:

ROUND(date, fmt)

Example:

SELECT ROUND(TIMESTAMP '2010-08-21', 'YY');
SELECT ROUND(TIMESTAMP '2010-08-21', 'MM');
SELECT ROUND(TIMESTAMP '2010-08-21', 'q');
SELECT ROUND(TIMESTAMP '2010-08-21', 'D');
SELECT ROUND(TIMESTAMP '2010-08-21 19:00:00', 'DD');

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session.

Syntax:

SESSIONTIMEZONE()

Example:

SELECT SESSIONTIMEZONE();

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides.

Syntax:

SYSDATE()

Example:

SELECT SYSDATE();

TO_CHAR

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

Syntax:

TO_CHAR(datetime, fmt)

Example:

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

TRUNC

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt.

Syntax:

TRUNC(date, fmt)

Example:

SELECT TRUNC(TIMESTAMP '2010-08-21', 'YY');
SELECT TRUNC(TIMESTAMP '2010-08-21', 'MM');
SELECT TRUNC(TIMESTAMP '2010-08-21', 'q');
SELECT TRUNC(TIMESTAMP '2010-08-21 19:00:00', 'DD');

TO_DATE

TO_DATE converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of DATE datatype.

Syntax:

TO_DATE(char, fmt)

Example:

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

NEW_TIME

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date.

Syntax:

NEW_TIME(date, timezone1, timezone2)

Example:

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