Formats for Datetime Values ​

The following formats (datetime masks) are supported in arguments to the following functions:

• TO_CHAR
• TO_DATE
• TO_TIMESTAMP

To convert Julian dates to the yyyy-mm-dd format, store the Julian dates as VARCHAR with a J preceeding the value. For example:

premdb=# create table yb100 (julian varchar(10));
CREATE TABLE

premdb=# insert into yb100 values ('J2456783');
INSERT 0 1

premdb=# SELECT julian::DATE from yb100;
   julian   
------------
 2014-05-05
(1 row)

Using the same table, Julian dates can be converted to the yyyy-mm-dd format using the TO_DATE function:

premdb=# SELECT TO_DATE(2456783::text, 'J');
  to_date   
------------
 2014-05-05
(1 row)

Separators in Date Formats ​

The basic date formats work with or without separator characters and with uppercase and lowercase letters; however, the literal date values and the corresponding date masks must match. Do not specify separators in the mask that do not exist in the input string, and do not use additional separator characters, such as extra spaces or hyphens, between format elements unless the input string also contains those characters.

For example, the following date masks are valid for a date such as 30 May 2016:

DD Mon YYYY
dd mon yyyy

However, the following masks will fail for the same input string:

DD-Mon-YYYY
DDMonYYYY
ddmonyyyy
dd  mon  yyyy

Year Formats ​

Do not use a four-digit year in the mask if the input string contains fewer digits. For example:

premdb=# SELECT TO_DATE('30 May 16 ', 'dd mon yyyy') from sys.const;
  to_date   
------------
 0016-05-30
(1 row)

The correct mask in this case would be dd mon yy.

Conversely, if the input value contains a four-digit year, the mask may specify YYYY, YYY, YY, or Y. For example:

premdb=# SELECT TO_DATE('30-May-2016', 'DD-Mon-Y') from sys.const;
  to_date   
------------
 2016-05-30
(1 row)

Handling of US Format (Microseconds) ​

The TO_TIMESTAMP function ignores any leading zeroes that appear in the format for microseconds. For example:

premdb=# select to_timestamp('20180228 11 59 59 000999 AM', 'YYYYMMDD HH12 MI SS US AM') from sys.const;
       to_timestamp        
----------------------------
 2018-02-28 11:59:59.999-08
(1 row)

Parent topic:Formatting Functions