Movatterモバイル変換

[0]ホーム
URL:

Categories:

Conversion functions ,Date & time functions

TO_DATE , DATE

Converts an input expression to a date:

  • For a VARCHAR expression, the result of converting the string to a date.

  • For a TIMESTAMP expression, the date from the timestamp.

  • For a VARIANT expression:

    • If the VARIANT contains a string, a string conversion is performed.

    • If the VARIANT contains a date, the date value is preserved as is.

    • If the VARIANT contains a JSON null value, the output is NULL.

  • For NULL input, the output is NULL.

For all other values, a conversion error is generated.

See also:

TRY_TO_DATE

Syntax

TO_DATE(<string_expr>[,<format>])TO_DATE(<timestamp_expr>)TO_DATE('<integer>')TO_DATE(<variant_expr>)DATE(<string_expr>[,<format>])DATE(<timestamp_expr>)DATE('<integer>')DATE(<variant_expr>)
Copy

Arguments

Required:

One of:

string_expr

String from which to extract a date. For example:'2024-01-31'.

timestamp_expr

A TIMESTAMP expression. The DATE portion of the TIMESTAMP value is extracted.

'integer'

An expression that evaluates to a string containing an integer. For example:'15000000'. Dependingon the magnitude of the string, it can be interpreted as seconds, milliseconds, microseconds, ornanoseconds. For details, see theUsage notes for this function.

variant_expr

An expression of type VARIANT.

The VARIANT must contain one of the following:

  • A string from which to extract a date.

  • A date.

  • A string containing an integer that represents the number of seconds or milliseconds.

Although TO_DATE accepts a TIMESTAMP value, it does not accept a TIMESTAMP value inside a VARIANT.

Optional:

format

Date format specifier forstring_expr orAUTO,which specifies that Snowflake automatically detects the format to use. For more information,seeDate and time formats in conversion functions.

The default is the current value of theDATE_INPUT_FORMATsession parameter (defaultAUTO).

Returns

The data type of the returned value is DATE. If the input is NULL, returns NULL.

Usage notes

  • The display format for dates in the output is determined by theDATE_OUTPUT_FORMATsession parameter (defaultYYYY-MM-DD).

  • If the format of the input parameter is a string that contains an integer:

    • After the string is converted to an integer, the integer is treated as a number of seconds, milliseconds,microseconds, or nanoseconds after the start of the Unix epoch (1970-01-01 00:00:00.000000000 UTC).

      • If the integer is less than 31536000000 (the number of milliseconds in a year), then the value is treated asa number of seconds.

      • If the value is greater than or equal to 31536000000 and less than 31536000000000, then the value is treatedas milliseconds.

      • If the value is greater than or equal to 31536000000000 and less than 31536000000000000, then the value istreated as microseconds.

      • If the value is greater than or equal to 31536000000000000, then the value istreated as nanoseconds.

    • If more than one row is evaluated (for example, if the input is the column name of a table that contains more thanone row), each value is examined independently to determine if the value represents seconds, milliseconds, microseconds, ornanoseconds.

Examples

The following examples use the TO_DATE and DATE functions.

Basic example

SELECTTO_DATE('2024-05-10'),DATE('2024-05-10');
Copy
+-----------------------+--------------------+| TO_DATE('2024-05-10') | DATE('2024-05-10') ||-----------------------+--------------------|| 2024-05-10            | 2024-05-10         |+-----------------------+--------------------+

Example that extracts the date from a timestamp

The TO_DATE function accepts TIMESTAMP values and strings in TIMESTAMP format, but discards the timeinformation (hours, minutes, and so on).

Create and load the table:

CREATEORREPLACETABLEdate_from_timestamp(tsTIMESTAMP);INSERTINTOdate_from_timestamp(ts)VALUES(TO_TIMESTAMP('2024.10.02 04:00:00','YYYY.MM.DD HH:MI:SS'));
Copy

Query the TIMESTAMP value in the table:

SELECTtsFROMdate_from_timestamp;
Copy
+-------------------------+| TS                      ||-------------------------|| 2024-10-02 04:00:00.000 |+-------------------------+

Query the TIMESTAMP value in the table using the TO_DATE function:

SELECTTO_DATE(ts)FROMdate_from_timestamp;
Copy
+-------------+| TO_DATE(TS) ||-------------|| 2024-10-02  |+-------------+

Examples that use different input formats

The following examples use the TO_DATE and DATE functions with different input formatspecifications. The date format in the returned output is determined by thesetting of theDATE_OUTPUT_FORMAT session parameter.

SELECTTO_DATE('2024.05.10','YYYY.MM.DD'),DATE('2024.05.10','YYYY.MM.DD');
Copy
+-------------------------------------+----------------------------------+| TO_DATE('2024.05.10', 'YYYY.MM.DD') | DATE('2024.05.10', 'YYYY.MM.DD') ||-------------------------------------+----------------------------------|| 2024-05-10                          | 2024-05-10                       |+-------------------------------------+----------------------------------+
SELECTTO_DATE('2024-05-10','AUTO'),DATE('2024-05-10','AUTO');
Copy
+-------------------------------+----------------------------+| TO_DATE('2024-05-10', 'AUTO') | DATE('2024-05-10', 'AUTO') ||-------------------------------+----------------------------|| 2024-05-10                    | 2024-05-10                 |+-------------------------------+----------------------------+
SELECTTO_DATE('05/10/2024','MM/DD/YYYY'),DATE('05/10/2024','MM/DD/YYYY');
Copy
+-------------------------------------+----------------------------------+| TO_DATE('05/10/2024', 'MM/DD/YYYY') | DATE('05/20/2024', 'MM/DD/YYYY') ||-------------------------------------+----------------------------------|| 2024-05-10                          | 2024-05-20                       |+-------------------------------------+----------------------------------+

Examples that use different output formats

The following examples show the results of queries when theDATE_OUTPUT_FORMATsession parameter is set toDD-MON-YYYY:

ALTERSESSIONSETDATE_OUTPUT_FORMAT='DD-MON-YYYY';
Copy
SELECTTO_DATE('2024-05-10','YYYY-MM-DD'),DATE('2024-05-10','YYYY-MM-DD');
Copy
+-------------------------------------+----------------------------------+| TO_DATE('2024-05-10', 'YYYY-MM-DD') | DATE('2024-05-10', 'YYYY-MM-DD') ||-------------------------------------+----------------------------------|| 10-May-2024                         | 10-May-2024                      |+-------------------------------------+----------------------------------+
SELECTTO_DATE('05/10/2024','MM/DD/YYYY'),DATE('05/10/2024','MM/DD/YYYY');
Copy
+-------------------------------------+----------------------------------+| TO_DATE('05/10/2024', 'MM/DD/YYYY') | DATE('05/10/2024', 'MM/DD/YYYY') ||-------------------------------------+----------------------------------|| 10-May-2024                         | 10-May-2024                      |+-------------------------------------+----------------------------------+

Examples that use a string that contains an integer

When the input is a string that contains an integer, the magnitude of that integer affects whether it is interpretedas seconds, milliseconds, etc. The following example shows how the function chooses the units to use (seconds, milliseconds,microseconds, or nanoseconds), based on the magnitude of the value.

Create and load the table:

CREATEORREPLACETABLEdemo1(descriptionVARCHAR,valueVARCHAR-- string rather than bigint);INSERTINTOdemo1(description,value)VALUES('Seconds','31536000'),('Milliseconds','31536000000'),('Microseconds','31536000000000'),('Nanoseconds','31536000000000000');
Copy

Pass the strings to the function:

SELECTdescription,value,TO_TIMESTAMP(value),TO_DATE(value)FROMdemo1ORDERBYvalue;
Copy
+--------------+-------------------+-------------------------+----------------+| DESCRIPTION  | VALUE             | TO_TIMESTAMP(VALUE)     | TO_DATE(VALUE) ||--------------+-------------------+-------------------------+----------------|| Seconds      | 31536000          | 1971-01-01 00:00:00.000 | 1971-01-01     || Milliseconds | 31536000000       | 1971-01-01 00:00:00.000 | 1971-01-01     || Microseconds | 31536000000000    | 1971-01-01 00:00:00.000 | 1971-01-01     || Nanoseconds  | 31536000000000000 | 1971-01-01 00:00:00.000 | 1971-01-01     |+--------------+-------------------+-------------------------+----------------+
Language:English
[8]ページ先頭
©2009-2025 Movatter.jp