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:
Date style arguments | Order arguments | Example |
---|---|---|
ISO
(ISO 8601/SQL standard) |
n/a |
|
GERMAN
|
n/a | 16.03.2016 00:00:00
|
SQL
|
MDY |
03/16/2016 00:00:00
|
DMY (default) |
16/03/2016 00:00:00
|
|
POSTGRES
|
MDY (default)
|
Wed Mar 16 00:00:00 2016
|
DMY
|
Wed 16 Mar 00:00:00 2016
|
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 theDATESTYLE
value. INTERVAL
input and output share the same format, with the following exceptions:- Units like
CENTURY
orWEEK
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 ]
- Units like
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)