Date and Time Input / Output

In this Topic:

Session Parameters for Dates, Times, and Timestamps

A set of session parameters determines how date, time, and timestamp data is passed into and out of Snowflake, as well as the time zone used in the time and timestamp formats that support time zones.

The parameters can be set at the account, user, and session levels. Execute the SHOW PARAMETERS command to view the current parameter settings that apply to all operations in the current session.

Input Formats

The following parameters define which date, time, and timestamp formats are recognized for DML, including COPY, INSERT, and MERGE operations:

The default for all three parameters is AUTO. When the parameter value is set to AUTO, Snowflake attempts to match date, time, or timestamp strings in any input expression with one of the formats listed in Supported Formats for AUTO Detection:

  • If a matching format is found, Snowflake accepts the string.
  • If no matching format is found, Snowflake returns an error.

Output Formats

The following parameters define the formats for date and time output from Snowflake:

In addition, the following parameter maps the TIMESTAMP data type alias to one of the three TIMESTAMP_* variations:

Time Zone

The following parameter determines the time zone:

File Format Options for Loading/Unloading Dates, Times, and Timestamps

Separate from the input and output format parameters, Snowflake provides three file format options to use when loading data into or unloading data from Snowflake tables:

  • DATE_FORMAT
  • TIME_FORMAT
  • TIMESTAMP_FORMAT

The options can be specified directly in the COPY command or in a named stage or file format object referenced in the COPY command. When specified, these options override the corresponding input formats (when loading data) or output formats (when unloading data).

Data Loading

When used in data loading, the options specify the format of the date, time, and timestamp strings in your staged data files. The options override the DATE_INPUT_FORMAT, TIME_INPUT_FORMAT, or TIMESTAMP_INPUT_FORMAT parameter settings.

The default for all these options is AUTO, meaning the COPY INTO table command attempts to match all date and timestamp strings in the staged data files with one of the formats listed in Supported Formats for AUTO Detection.

  • If a matching format is found, Snowflake accepts the string.
  • If no matching format is found, Snowflake returns an error and then performs the action specified for the ON_ERROR copy option.

Important

Snowflake supports automatic detection of most common date, time, and timestamp formats (see tables below); however, some formats may produce ambiguous results, which can cause Snowflake to apply an incorrect format when using AUTO for data loading.

To guarantee correct loading of data, we recommend explicitly setting the file format options for data loading.

Data Unloading

When used in data unloading, the options specify the format applied to the dates, times, and timestamps unloaded to the files in specified staging location.

The default for all these options is AUTO, meaning Snowflake applies the formatting specified in the following parameters:

  • DATE_OUTPUT_FORMAT
  • TIME_OUTPUT_FORMAT
  • TIMESTAMP_*_OUTPUT_FORMAT (depending on the TIMESTAMP_TYPE_MAPPING setting)

Supported Formats for AUTO Detection

If instructed to do so, Snowflake automatically detects and processes the following formats for date, time, and timestamp input strings.

Date Formats

Format Example
ISO Date Formats  
YYYY-MM-DD 2013-04-28
Other Date Formats  
DD-MON-YYYY 17-DEC-1980
MM/DD/YYYY [1] 12/17/1980
[1]Could produce incorrect dates when loading or operating on dates in common European (DD/MM/YYYY) formats. For example, 05/02/2013 could be interpreted as May 2, 2013 instead of February 5, 2013.

Time Formats

Format Example
ISO Time Formats  
HH24:MI:SS.FFTZH:TZM 20:57:01.123456789+07:00
HH24:MI:SS.FF 20:57:01.123456789
HH24:MI:SS 20:57:01
HH24:MI 20:57
Other Time Formats  
HH12:MI:SS.FF AM 07:57:01.123456789 PM
HH12:MI:SS AM 04:01:07 PM
HH12:MI AM 04:01 PM

Timestamp Formats

Format Example
ISO Timestamp Formats  
YYYY-MM-DD”T”HH24:MI:SS.FFTZH:TZM 2013-04-28T20:57:01.123456789+07:00
YYYY-MM-DD HH24:MI:SS.FFTZH 2013-04-28 20:57:01.123456789+07
YYYY-MM-DD HH24:MI:SS.FFTZH:TZM 2013-04-28 20:57:01.123456789+07:00
YYYY-MM-DD HH24:MI:SS.FF TZH:TZM 2013-04-28 20:57:01.123456789 +07:00
YYYY-MM-DD HH24:MI:SS.FF TZHTZM 2013-04-28 20:57:01.123456789 +07:00
YYYY-MM-DD HH24:MI:SS TZH:TZM 2013-04-28 20:57:01.123456789 +07:00
YYYY-MM-DD”T”HH24:MI:SSTZH:TZM 2013-04-28T20:57:01-07:00
YYYY-MM-DD HH24:MI:SSTZH:TZM 2013-04-28T20:57:01-07:00
YYYY-MM-DD HH24:MI:SSTZH 2013-04-28T20:57:01-07
YYYY-MM-DD HH24:MI:SS TZHTZM 2013-04-28 20:57:01 +0700
YYYY-MM-DD”T”HH24:MITZH:TZM 2013-04-28T20:57+07:00
YYYY-MM-DD HH24:MITZH:TZM 2013-04-28T20:57+07:00
YYYY-MM-DD”T”HH24:MI:SS.FF 2013-04-28T20:57:01.123456
YYYY-MM-DD HH24:MI:SS.FF 2013-04-28 20:57:01.123456
YYYY-MM-DD”T”HH24:MI:SS 2013-04-28T20:57:01
YYYY-MM-DD HH24:MI:SS 2013-04-28 20:57:01
YYYY-MM-DD”T”HH24:MI 2013-04-28T20:57
YYYY-MM-DD HH24:MI 2013-04-28T20:57
YYYY-MM-DD”T”HH24 2013-04-28T20
YYYY-MM-DD HH24 2013-04-28T20
Internet (RFC) Timestamp Formats  
DY, DD MON YYYY HH24:MI:SS.FF TZHTZM Thu, 21 Dec 2000 16:01:07 +0200
DY, DD MON YYYY HH24:MI:SS TZHTZM Thu, 21 Dec 2000 16:01:07 +0200
DY, DD MON YYYY HH24:MI:SS.FF Thu, 21 Dec 2000 16:01:07 +0200
DY, DD MON YYYY HH24:MI:SS Thu, 21 Dec 2000 16:01:07 +0200
DY, DD MON YYYY HH12:MI:SS.FF AM TZHTZM Thu, 21 Dec 2000 04:01:07 PM +0200
DY, DD MON YYYY HH12:MI:SS AM TZHTZM Thu, 21 Dec 2000 04:01:07 PM +0200
DY, DD MON YYYY HH12:MI:SS.FF AM Thu, 21 Dec 2000 04:01:07 PM +0200
DY, DD MON YYYY HH12:MI:SS AM Thu, 21 Dec 2000 04:01:07 PM +0200
Other Timestamp Formats  
MM/DD/YYYY HH24:MI:SS [2] 2/18/2008 02:36:48
DY MON DD HH24:MI:SS TZHTZM YYYY Mon Jul 08 18:09:51 +0000 2013
[2]Could produce incorrect dates when loading or operating on dates in common European (DD/MM/YYYY) formats. For example, 05/02/2013 could be interpreted as May 2, 2013 instead of February 5, 2013.

Date & Time Functions

Snowflake provides a set of functions to construct, convert, extract, or modify DATE/TIME/TIMESTAMP data. For information, see Date & Time Functions.