In this Topic: Show
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:
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:
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.
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:
Supported Formats for AUTO Detection¶If instructed to do so, Snowflake automatically detects and processes specific formats for date, time, and timestamp input strings. Attention Some strings can match multiple formats. For example, ‘07-04-2016’ is compatible with both 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. About the Format Specifiers in This Section¶The next sections describe the date, time, and timestamp formats by using the following format specifiers:
The time zone offset can be negative or positive. For example, “-0800” is 8 hours behind (West of) UTC. For more details about valid ranges, etc., see Additional Information. Date Formats¶
When using AUTO date formatting, dashes and slashes are not interchangeable. Slashes imply Time Formats¶
The When a timezone offset (e.g. “0800”) occurs immediately after a digit in a time or timestamp string, the timezone offset must start with Timestamp 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 Tip In some of the timestamp formats, the letter The double quotes around the The double quotes should be used only in the format specifier, not the actual values. For example: SELECT TO_TIMESTAMP('2019-02-28T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS'); In addition, the quotes around the 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:
Using the Correct Number of Digits with Format Elements¶For most fields (year, month, day, hour, minute, and second), the elements ( The following rules tell you how many digits you should actually specify in the literal values:
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 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, Tip Although some whitespace differences are allowed in order to handle variably-formatted data, Snowflake recommends that values and specifiers exactly match, 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:
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 unit of measurement based on the length of the value. Note The use of quoted integers as inputs is deprecated. For example, to calculate the timestamp equivalent to 1487654321 seconds since the start of the Unix epoch: select to_timestamp('1487654321'); -------------------------------+ TO_TIMESTAMP('1487654321') | -------------------------------+ 2017-02-20 21:18:41.000000000 | -------------------------------+ Here is a similar calculation using milliseconds since the start of the epoch: select to_timestamp('1487654321321'); -------------------------------+ TO_TIMESTAMP('1487654321321') | -------------------------------+ 2017-02-20 21:18:41.321000000 | -------------------------------+ Depending on the magnitude of the value, Snowflake uses a different unit of measure:
In cases where formatted strings and integers in strings are passed to the function, each value is cast according to the contents of the string. For example, if you pass a date-formatted string and a string containing an integer to TO_TIMESTAMP, the function interprets each value correctly according to what each string contains: select to_timestamp(column1) from values ('2013-04-05'), ('1487654321'); +-------------------------+ | TO_TIMESTAMP(COLUMN1) | |-------------------------| | 2013-04-05 00:00:00.000 | | 2017-02-21 05:18:41.000 | +-------------------------+ Date & Time Function Format Best Practices¶AUTO detection usually determines the correct input format; however, there are situations where it might not be able to make the correct determination. To avoid this, Snowflake strongly recommends the following best practices (substituting TO_DATE , DATE or TO_TIME , TIME for TO_TIMESTAMP, as appropriate):
Which date formats are allowed in Excel?Microsoft Excel has two default formats for dates and time - short and long, as explained in default Excel date format. To quickly change date format in Excel to the default formatting, do the following: Select the dates you want to format.
How do you write Jan 22 in Excel?Creating a custom date format in Excel. In an Excel sheet, select the cells you want to format.. Press Ctrl+1 to open the Format Cells dialog.. On the Number tab, select Custom from the Category list and type the date format you want in the Type box.. Click OK to save the changes.. How do I change the default date format to MM DD YYYY in Excel?Click the Start button, and then click Control Panel.. Click Region and Language.. In the Region dialog box, click Additional settings.. Click the Date tab.. In the Short date format list, click a format that uses four digits for the year ("yyyy").. Click OK.. What is the default clock format in Excel?Excel default date time format yyyy-mm-dd hh:mm:ss versus yyyy-mm-dd hh:mm.
|