TO_DATE

Syntax

TO_DATE
(
  str  IN text
)
RETURNS timestamp;

TO_DATE
(
  str  IN text,
  fmt  IN text
)
RETURNS timestamp;

TO_DATE
(
  num  IN integer,
  fmt  IN text
)
RETURNS timestamp;

Overview

The TO_DATE function converts text (or integer values converted to strings) to TIMESTAMP(0)(date to hours, minutes, and seconds without milliseconds) values in Oracle style date/time format.

If a format model (FMT) is provided, the date is interpreted according to that format; if not, it is converted according to the default format.

Returns NULL if an empty string is keyed in.

Unlike Oracle, it doesn't accept variables of type date to preserve type safety.

Parameter

Parameter	Description
str	text type; String containing date and time information. If it is an empty string, it is treated as NULL.
num	integer type; Integer value that is internally converted to a string and interpreted as a date.
fmt	text type; Format model that specifies the date/time format of the input string. For example, use ‘YYYY-MM-DD HH24:MI:SS’. For supported format models, refer to the PG official documentation. Date/time format models

Example

-- Convert date/time strings to TIMESTAMP using text and format models
SELECT oracle.TO_DATE('2023-06-01 12:34:56', 'YYYY-MM-DD HH24:MI:SS');

       to_date       
---------------------
 2023-06-01 12:34:56
(1 row)

-- Use the default conversion format if only text is passed (empty strings return NULL)
SELECT oracle.TO_DATE('2023-06-01 12:34:56');

       to_date       
---------------------
 2023-06-01 12:34:56
(1 row)

-- Convert integer values to strings and convert them to TIMESTAMP
SELECT oracle.TO_DATE(20230601, 'YYYYMMDD');

       to_date       
---------------------
 2023-06-01 00:00:00
(1 row)