Date and Time Formatting Functions

The following table describes the functions that convert strings to specific data types and vice versa. Those functions are used when the required input/output format of a specific DATE/TIMESTAMP is different from the default DATE/TIMESTAMP formats. The default DATE format is: YYYY-MM-DD The default TIMESTAMP format is: YYYY-MM-DD HH24:MI:SS.MS In all the functions below, the first argument is the value to be converted and the second argument is a template that defines the output or input format. The input string is of one of the Character types.

Function	Description and Comments	Example
`TO_DATE(STRING, format)`	Converts string to `DATE` according to the given format Returns `DATE`	`to_date('21 Jun 2023', 'dd mon YYYY')` => 2023-06-21
`TO_TIMESTAMP(STRING, format)`	Converts string to `TIMESTAMP` according to the given format Returns `TIMESTAMP`	`TO_TIMESTAMP('_2023-12-13_ 04:05:06.789', 'yyyy-dd-mm hh24:mi:ss.ms')` => 2023-12-13 04:05:06.789
`TO_CHAR(DATE, format)` `TO_CHAR(TIMESTAMP, format)`	Converts a `DATE` or `TIMESTAMP` data type to a string in the given format Returns `VARCHAR` See notes	`TO_CHAR(date '2022-12-12', 'yy-mm-dd')` => 22-12-12 `TO_CHAR(TIMESTAMP '2022-12-24 16:27:30', 'dd-mm-yyyy HH12:MI:SS am')` => 24-12-2022 04:27:30 pm

Template Patterns for Date/Time Formatting:

Pattern	Description and Comments
`HH`	hour of day (01-12)
`HH12`	hour of day (01-12)
`HH24`	hour of day (00-23)
`MI`	minute (00-59)
`SS`	second (00-59)
`MS`	millisecond (000-999)
`US`	microsecond (000000-999999)
`YYYY`	year (4 digits)
`YY`	last 2 digits of year
`MM`	month number (01-12)
`Mon`	First three letters of the month (‘Jan’, ‘Feb’, and so on)
`DD`	day of month (01-31)
`AM`, `am`, `PM`, `pm`	meridiem indicator

Examples

Convert the input format to a DATE/TIMESTAMP that can identified as such:

SELECT TO_DATE('2012-JAN-12','YYYY-MON-DD');
SELECT TO_DATE('20/12/12','YY/MM/DD');
SELECT TO_DATE('2012.07.23', 'YYYY.MM.DD');
SELECT TO_DATE('20200526','YYYYMMDD');
SELECT TO_TIMESTAMP('20/12/12 17:12:12.45','YY/MM/DD HH24:MI:SS.MS');
SELECT TO_TIMESTAMP('2010-Aug-09 12:00:01 PM' , 'yyyy-Mon-dd HH:MI:SS PM');

Convert the DATE/TIMESTAMP to the desired output format:

SELECT TO_CHAR(TIMESTAMP '2022-12-12 16:27:30.41', 'dd-mm-yyyy HH12:MI:SS
am');
SELECT TO_CHAR(date_column, 'YY-MM-DD HH:MI:SS.MS AM') FROM my_table;

CAST Aggregate Functions

⌘I

SQL Specification

SQL Syntax Conventions

Data Types

SQL Functions and Operators

Data Definition Language (DDL) Commands

Data Manipulation Language Commands

Transaction Control Language Commands

Data Query Language

Hints

Managing Users

System Monitoring

System Management

Appendices

Date and Time Formatting Functions

Examples

​Examples

Examples