SET DATESTYLE

Specifies how to format date/time output for the current session. Use SHOW DATESTYLE to verify the current output settings.

Syntax

SET DATESTYLE TO { arg | 'arg' }[, arg | 'arg' ]

Parameters

SET DATESTYLE has a single parameter, which can be set to one or two arguments that specify date ordering and style. Each argument can be specified singly or in combination with the other; if combined, they can be specified in any order.

The following table describes each style and the date ordering arguments it supports:

Vertica ignores the order argument for date styles ISO and GERMAN. If the date style is SQL or POSTGRES, the order setting determines whether dates are output in MDY or DMY order. Neither SQL nor POSTGRES support YMD order. If you specify YMD for SQL or POSTGRES, Vertica ignores it and uses their default MDY order.

Date styles and ordering can also affect how Vertica interprets input values. For more information, see Date/Time Literals.

Privileges

None

Input Dependencies

In some cases, input format can determine output, regardless of date style and order settings:

• Vertica ISO output for DATESTYLE is ISO long form, but several input styles are accepted. If the year appears first in the input, YMD is used for input and output, regardless of the DATESTYLE value.
• INTERVAL input and output share the same format, with the following exceptions:
  • Units like CENTURY or WEEK are converted to years and days.
  • AGO is converted to the appropriate sign.

  If the date style is set to ISO, output follows this format:

  [ quantity unit [ ... ] ] [ days ] [ hours:minutes:seconds ]

Example

=> CREATE TABLE t(a DATETIME);
CREATE TABLE
=> INSERT INTO t values ('3/16/2016');
 OUTPUT
--------
      1
(1 row)

=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | ISO, MDY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 2016-03-16 00:00:00
(1 row)

=> SET DATESTYLE TO German;
SET
=> SHOW DATESTYLE;
   name    |   setting
-----------+-------------
 datestyle | German, DMY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 16.03.2016 00:00:00
(1 row)

=> SET DATESTYLE TO SQL;
SET
=> SHOW DATESTYLE;
   name    | setting
-----------+----------
 datestyle | SQL, DMY
(1 row)

=> SELECT * FROM t;
          a
---------------------
 16/03/2016 00:00:00
(1 row)

=> SET DATESTYLE TO Postgres, MDY;
SET
=> SHOW DATESTYLE;
   name    |    setting
-----------+---------------
 datestyle | Postgres, MDY
(1 row)

=> SELECT * FROM t;
            a
--------------------------
 Wed Mar 16 00:00:00 2016
(1 row)