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.

Warning

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

To guarantee correct loading of data, Snowflake strongly recommends 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 stage.

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.

Caution

Some strings can match multiple formats. For example, ‘07-04-2016’ is compatible with both ‘MM-DD-YYYY’ and ‘DD-MM-YYYY’, but has different meanings in each format (July 4 vs. April 7). The fact that a matching format is found does not guarantee that the string is parsed as the user intended.

Although automatic date format detection is convenient, it increases the possibility of dates being misinterpreted. Snowflake strongly recommends specifying the format explicitly rather than relying on automatic date detection.

Date Formats

Format

Example

Notes

ISO Date Formats

YYYY-MM-DD

2013-04-28

Other Date Formats

DD-MON-YYYY

17-DEC-1980

MM/DD/YYYY

12/17/1980

Could produce incorrect dates when loading or operating on dates in common European formats (i.e. DD/MM/YYYY). For example, 05/02/2013 could be interpreted as May 2, 2013 instead of February 5, 2013.

When using AUTO date formatting, dashes and slashes are not interchangeable. Slashes imply MM/DD/YYYY format, and dashes imply YYYY-MM-DD format. Strings such as ‘2019/01/02’ or ‘01-02-2019’ are not interpreted as you might expect.

Time Formats

Format

Example

Notes

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

Internet (RFC) Time Formats

HH12:MI:SS.FF AM

07:57:01.123456789 AM

HH12:MI:SS AM

04:01:07 AM

HH12:MI AM

04:01 AM

The “AM” format specifier allows values with either “AM” or “PM”.

Timestamp Formats

Format

Example

Notes

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:TZM

2013-04-28 20: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.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 +0700

YYYY-MM-DD HH24:MI:SS TZH:TZM

2013-04-28 20:57:01 +07:00

YYYY-MM-DD HH24:MI:SS TZHTZM

2013-04-28 20:57:01 +0700

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-28 20:57

YYYY-MM-DD”T”HH24

2013-04-28T20

YYYY-MM-DD HH24

2013-04-28 20

YYYY-MM-DD”T”HH24:MI:SSTZH:TZM

2013-04-28T20:57:01-07:00

YYYY-MM-DD HH24:MI:SSTZH:TZM

2013-04-28 20:57:01-07:00

YYYY-MM-DD HH24:MI:SSTZH

2013-04-28 20:57:01-07

YYYY-MM-DD”T”HH24:MITZH:TZM

2013-04-28T20:57+07:00

YYYY-MM-DD HH24:MITZH:TZM

2013-04-28 20:57+07:00

Internet (RFC) Timestamp Formats

DY, DD MON YYYY HH24:MI:SS TZHTZM

Thu, 21 Dec 2000 16:01:07 +0200

DY, DD MON YYYY HH24:MI:SS.FF TZHTZM

Thu, 21 Dec 2000 16:01:07.123456789 +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 TZHTZM

Thu, 21 Dec 2000 04:01:07.123456789 PM +0200

DY, DD MON YYYY HH24:MI:SS

Thu, 21 Dec 2000 16:01:07

DY, DD MON YYYY HH24:MI:SS.FF

Thu, 21 Dec 2000 16:01:07.123456789

DY, DD MON YYYY HH12:MI:SS AM

Thu, 21 Dec 2000 04:01:07 PM

DY, DD MON YYYY HH12:MI:SS.FF AM

Thu, 21 Dec 2000 04:01:07.123456789 PM

Other Timestamp Formats

MM/DD/YYYY HH24:MI:SS

2/18/2008 02:36:48

Could produce incorrect dates when loading or operating on dates in common European formats (i.e. DD/MM/YYYY). For example, 05/02/2013 could be interpreted as May 2, 2013 instead of February 5, 2013.

DY MON DD HH24:MI:SS TZHTZM YYYY

Mon Jul 08 18:09:51 +0000 2013

Note

In some of the examples above, the letter “T” is used as a separator between the date and the time in a timestamp, e.g. ‘2019-03-01”T”23:59:59’.

The double quotes around the “T” are optional. However, Snowflake recommends using double quotes around the “T” or other literal to avoid ambiguity.

The double quotes should be in only the format specifier, not the actual values. The following is correct:

SELECT TO_TIMESTAMP('2019-02-31T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');

The quotes around the “T” should be double quotes, not single quotes.

Additional Information

The following sections describe requirements and best practices for individual fields in dates, times, and timestamps.

Valid Ranges of Values for Fields

The recommended ranges of values for each field are shown below:

Field

Values

Notes

Years

0001-9999

Some values outside this range might be accepted in some contexts, but Snowflake recommends using only values in this range. For example, the year 0000 is accepted, but is incorrect because in the Gregorian calendar the year 1 A.D. comes immediately after the year 1 B.C.; there is no year 0.

Months

01-12

Days

01-31

In some contexts, Snowflake accepts values from 01-31 for every month, not just months that have 31 days. Excessive values are rolled over. For example, ‘2019-02-31’ becomes March 3, 2019 (equivalent to ‘2019-03-03’). Rollover is deprecated. Snowflake recommends specifying only values between 01 and the actual number of days in the month.

Hours

00-23

Or 00-11 if you are using HH12 format.

Minutes

00-59

Seconds

00-59

Snowflake allows the values 60 and 61 in some contexts. However, Snowflake recommends not using those values. Snowflake does not support leap seconds, and the values 60 and 61 are rolled over. For example, the time ‘01:02:60’ becomes the equivalent of ‘01:03:00’.

Fraction

0-999999999

The number of digits after the decimal point depends in part upon the exact format specifier. E.g. FF3 supports up to 3 digits after the decimal point and FF9 supports up to 9 digits after the decimal point. You can enter fewer digits than you specified (e.g. 1 digit is allowed even if you use FF9); trailing zeroes are not required to fill out the field to the specified width.

Using the Correct Number of Digits with Format Elements

For most fields (year, month, day, hour, minute, and second), the elements (‘YYYY’, ‘MM’, ‘DD’, etc.) of the format specifier are two or four characters. The following rules tell you how many digits you should actually specify in the literal values.

  • YYYY: You can specify 1, 2, 3, or 4 digits of the year. However, Snowflake recommends specifying 4 digits. If necessary, prepend leading zeros. For example, the year 536 A.D. would be ‘0536’.

  • YY: Specify 1 or 2 digits of the year. However, Snowflake recommends specifying 2 digits. If necessary, prepend a leading zero.

  • MM: Specify one or two digits. For example, January can be represented as ‘01’ or ‘1’. Snowflake recommends using two digits.

  • DD: Specify one or two digits. Snowflake recommends using two digits.

  • HH: Specify one or two digits. Snowflake recommends using two digits.

  • MI: Specify one or two digits. Snowflake recommends using two digits.

  • SS: Specify one or two digits. Snowflake recommends using two digits.

  • FF9: Specify between 1 and 9 digits (inclusive). Snowflake recommends specifying the number of actual significant digits. Trailing zeroes are not required.

  • TZH: Specify one or two digits. Snowflake recommends using two digits.

  • TZM: Specify one or two digits. Snowflake recommends using two digits.

For all fields (other than fractional seconds), Snowflake recommends specifying the maximum number of digits. Use leading zeroes if necessary. E.g. ‘0001-02-03 04:05:06 -07:00’ follows the recommended format.

For fractional seconds, trailing zeroes are optional. In general, it is considered good practice to specify only the number of digits that are reliable and meaningful. For example, if a time measurement is accurate to 3 decimal places (milliseconds), then specifying it as 9 digits (e.g. ‘.123000000’) might be misleading to the reader.

Whitespace In Values and Format Specifiers

Snowflake enforces matching whitespace in some, but not all, situations. For example, the following statement generates an error because there is no space between the days and the hours in the specified value, but there is a space between the “DD” and the “HH” in the format specifier:

SELECT TO_TIMESTAMP('2019-02-2823:59:59 -07:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM');

However, the following statement does not generate an error, even though the value contains a whitespace where the specifier does not:

SELECT TO_TIMESTAMP('2019-02-28 23:59:59.000000000 -07:00', 'YYYY-MM-DDHH24:MI:SS.FF TZH:TZM');

The reason for the difference is that in the former case, the values would be ambiguous if the fields are not all at their maximum width. For example, ‘213’ could be interpreted as 2 days and 13 hours, or as 21 days and 3 hours. However, “DDHH” is unambiguously the same as “DD HH” (other than the whitespace).

Tip

Although some whitespace differences are allowed in order to handle variably-formatted data, Snowflake recommends that values and specifiers match exactly, including spaces.

Context Dependency

Not all restrictions are enforced equally in all contexts. For example, some expressions might roll over February 31, while others might not.

Summary of Best Practices

These best practices minimize ambiguities and other potential issues in past, current, and projected future versions of Snowflake:

  • Be aware of the dangers of mixing data from sources that use different formats, e.g. of mixing data that follows the common U.S. format ‘MM-DD-YYYY’ and the common European format ‘DD-MM-YYYY’.

  • Specify the maximum number of digits for each field (except fractional seconds). For example, use 4-digit years, specifying leading zeroes if necessary.

  • Specify a blank or the letter “T” between the date and time in a timestamp.

  • Whitespace (and the optional “T” separator between the date and time) should be the same in values and in the format specifier.

  • Do not enter a value of more than 59 for seconds. Snowflake does not store leap seconds.

  • Do not enter a day-of-the-month value larger than the number of days in the month. For example, do not enter February 30.

  • Use interval arithmetic if you need the equivalent of rollover.

  • Be careful when using AUTO formatting. When possible, specify the format, and ensure that values always match the specified format.

  • Specifying the format in the command is better than specifying the format outside the command, for example in a parameter such as DATE_INPUT_FORMAT. (See below.)

  • When moving scripts from one environment to another, ensure that date-related parameters, such as DATE_INPUT_FORMAT, are the same in the new environment as they were in the old environment (assuming that the values are also in the same format).

Date & Time Functions

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

AUTO Detection of Integer-stored Date, Time, and Timestamp Values

For integers of seconds or milliseconds stored in a string, Snowflake attempts to determine the correct time measurement based on the length of the value. For example:

select to_timestamp('1487654321');
-------------------------------+
  TO_TIMESTAMP('1487654321')   |
-------------------------------+
 2017-02-20 21:18:41.000000000 |
-------------------------------+

select to_timestamp('1487654321321');
-------------------------------+
 TO_TIMESTAMP('1487654321321') |
-------------------------------+
 2017-02-20 21:18:41.321000000 |
-------------------------------+

Depending on the target data type, a different input scale is assumed:

  • DATE and TIMESTAMP: Snowflake attempts to detect the scale of the input integer (seconds, milliseconds, etc).

  • TIME: Snowflake uses seconds as the scale.

To reduce the chance of an incorrect format detection, Snowflake raises an error if an integer-only format is detected along with any other format. For example:

select to_timestamp(column1) from values('1487654321');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2017-02-20 21:18:41.000000000 |
-------------------------------+

select to_timestamp(column1) from values('2013-04-05');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2013-04-05 00:00:00.000000000 |
-------------------------------+

select to_timestamp(column1) from values('1487654321'),('2013-04-05');
FAILURE: Timestamp '2013-04-05' is not recognized

select to_timestamp(column1) from values ('2013-04-05'), ('1487654321');
FAILURE: Timestamp '1487654321' is not recognized

Warning

To avoid unexpected results, do not mix different scales of integers in the same column, such as second-scale and millisecond-scale integers. For example:

select to_timestamp(column1) from values('1487654321321'),('1487654321');
-------------------------------+
     TO_TIMESTAMP(COLUMN1)     |
-------------------------------+
 2017-02-20 21:18:41.321000000 |
 1970-01-17 21:14:14.321000000 |
-------------------------------+

select to_timestamp(column1) from values('1487654321'),('1487654321321');
--------------------------------+
     TO_TIMESTAMP(COLUMN1)      |
--------------------------------+
 2017-02-20 21:18:41.000000000  |
 49111-11-21 23:28:41.000000000 |
--------------------------------+

Date & Time Function Format Best Practices

AUTO detection usually determines the correct input format; however, there are situations where it might make a wrong decision.

To avoid this, we strongly recommend the following best practices (substituting TO_DATE or TO_TIME for TO_TIMESTAMP, as appropriate):

  • Avoid using AUTO format. Specify an explicit format string by:

  • For strings containing integer values, specify the scale using the following syntax:

    TO_TIMESTAMP(TO_NUMBER(<string_column>), <scale>)