Date and time input and output formats¶

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.

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).

Date formats¶

For descriptions of the elements used in the formats below, see About the elements used in input and output formats.

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¶

For descriptions of the elements used in the formats below, see About the elements used in input and output formats.

The AM format element allows values with either AM or PM.

When a timezone offset (e.g. “0800”) occurs immediately after a digit in a time or timestamp string, the timezone offset must start with + or -. The sign prevents ambiguity when the fractional seconds or the time zone offset does not contain the maximum number of allowable digits. For example, without a separator between the last digit of the fractional seconds and the first digit of the timezone, the “1” in the time “04:04:04.321200” could be either the last digit of the fractional seconds (e.g. 321 milliseconds) or the first digit of the timezone offset (e.g. 12 hours ahead of UTC).

Timestamp formats¶

For descriptions of the elements used in the formats below, see About the elements used in input and output formats.

When a timezone offset (e.g. “0800”) occurs immediately after a digit in a time or timestamp string, the timezone offset must start with + or -. The sign prevents ambiguity when the fractional seconds or the time zone offset does not contain the maximum number of allowable digits. For example, without a separator between the last digit of the fractional seconds and the first digit of the timezone, the “1” in the time “04:04:04.321200” could be either the last digit of the fractional seconds (e.g. 321 milliseconds) or the first digit of the timezone offset (e.g. 12 hours ahead of UTC).

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

Valid ranges of values for fields¶

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

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.

• HH12 and HH24: 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 zeros 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 zeros if necessary. For example, ‘0001-02-03 04:05:06 -07:00’ follows the recommended format.

For fractional seconds, trailing zeros 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.

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 DD and 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).

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 for specifying the format¶

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 zeros 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.

• 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 safer 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).