Introduction
- It's important to set the timezone properly in your Snowflake account.
- What are the date datatypes in Snowflake? And how can we format the Date data types?
- A bit of arithmetic with date datatypes.
- Several sections about the snowflake Date functions.
Timezones in Snowflake
SHOW PARAMETERS LIKE '%TIMEZONE%' IN ACCOUNT; --America/Los_Angeles
USE ROLE ACCOUNTADMIN;
ALTER ACCOUNT SET TIMEZONE = 'Europe/Amsterdam';
USE ROLE SYSADMIN;
SHOW PARAMETERS LIKE '%TIMEZONE%' IN ACCOUNT; --Europe/Amsterdam 
Snowflake Date Data types
|
Date datatypes |
Description |
|
DATE |
Snowflake supports a single DATE data type for storing dates (with
no time elements). |
|
DATETIME |
DATETIME is an alias for TIMESTAMP_NTZ |
|
TIME |
Snowflake supports a single TIME data type for storing times. |
|
TIMESTAMP |
TIMESTAMP in Snowflake is a user-specified alias associated with
one of the TIMESTAMP_* variations (specified by the TIMESTAMP_TYPE_MAPPING session parameter). |
|
TIMESTAMP_LTZ |
TIMESTAMP_LTZ internally stores UTC time with a specified
precision. |
|
TIMESTAMP_NTZ |
TIMESTAMP_NTZ internally stores “wallclock” time with a specified
precision. |
|
TIMESTAMP_TZ |
TIMESTAMP_TZ internally stores UTC time together with an
associated time zone offset. |
Here is an example of the date datatypes.
SELECT '2021-05-23 12:00:00'::DATE, '2021-05-23 12:00:00'::DATETIME, '2021-05-23 12:00:00'::TIMESTAMP, '12:00:00'::TIME, '2021-05-23 12:00:00'::TIMESTAMP_LTZ, '2021-05-23 12:00:00'::TIMESTAMP_NTZ, '2021-05-23 12:00:00'::TIMESTAMP_TZ;
This results in :
You can change the format of the date datatypes with parameters DATE_OUTPUT_FORMAT, DATE_INPUT_FORMAT, TIMESTAMP_INPUT_FORMAT, TIMESTAMP_LTZ_OUTPUT_FORMAT, TIMESTAMP_NTZ_OUTPUT_FORMAT, TIMESTAMP_OUTPUT_FORMAT, TIMESTAMP_TZ_OUTPUT_FORMAT. It is possible to set these parameters on Account, Session and Object.
Let me show an example :
--First reset the SESSION DATE_INPUT_FORMAT AND DATE_OUTPUT_FORMAT
ALTER SESSION UNSET DATE_INPUT_FORMAT;
ALTER SESSION UNSET DATE_OUTPUT_FORMAT;
--Show the SESSION parameters DATE_INPUT_FORMAT AND DATE_OUTPUT_FORMAT
SHOW PARAMETERS LIKE '%DATE_%_FORMAT%' IN SESSION;
Results in :
Now with the default settings let's try to 'ingest' a date with the format YYYYMMDD.
SELECT '20210523'::DATE;
Resulting in a date 1970-08-22.
Snowflake doesn't seem to understand the format. Let's try to tell Snowflake to understand the input format and present in a more format we use to in The Netherlands.
--Change the DATE_INPUT_FORMAT and DATE_OUTPUT_FORMAT
ALTER SESSION SET DATE_INPUT_FORMAT = 'YYYYMMDD';
ALTER SESSION SET DATE_OUTPUT_FORMAT = 'DD-MM-YYYY';
Resulting in :
Snowflake now understands the format and it presents it in another format. You can do that with the TIMESTAMP datatype too. Try it out yourself.
Precision of the Timestamp datetype
SELECT TO_TIMESTAMP('2020-05-25 11:58:11.6064278', 'YYYY-MM-DD HH24:MI:SS.FF9');
Results :
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF9';
SELECT TO_TIMESTAMP('2020-05-25 11:58:11.6064278', 'YYYY-MM-DD HH24:MI:SS.FF9');
Result :
The precision is now 9.
And we can go back to the defaults with the UNSET command.
ALTER SESSION UNSET TIMESTAMP_NTZ_OUTPUT_FORMAT;
Date arithmetic
Now an example with date arithmetic.
SELECT CURRENT_DATE() AS D,
D + 1;
Results in :
Snowflake Date Functions
|
Date Functions |
Description |
Return |
|
SECOND (time or timestamp) |
Extracts the corresponding time part seconds from a time
or timestamp value. |
0 to 59 |
|
MINUTE (time or timestamp) |
Extracts the corresponding time part minute from a time
or timestamp value. |
0 to 59 |
|
HOUR (time or timestamp) |
Extracts the corresponding time part hour from a time or
timestamp value. |
0 to 23 |
|
DAYNAME
(date or Timestamp) |
Extracts the three-letter day-of-week name from the specified date
or timestamp |
Sun, Mon, Tue, Wed, Thu, Fri, Sat |
|
DAY (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 31 |
|
DAYOFMONTH
(date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 31 |
|
DAYOFYEAR (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 366 |
|
WEEK (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 54 |
|
WEEKISO (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 53 |
|
YEAROFWEEK
(date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
Any valid year |
|
YEAROFWEEKISO (date or
Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
Any valid year |
|
MONTHNAME (date
or Timestamp) |
Extracts the three-letter month name from the specified date or
timestamp |
Jan, Feb,Mar, APR, May, Jun, Jul,
Aug, Sep, Oct, Nov, Dec |
|
QUARTER (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
1 to 4 |
|
YEAR (date or Timestamp) |
Extracts the corresponding date part from a date or timestamp. |
Any valid year |
|
LAST_DAY (date
or time [, date_part]) |
Returns the last day of the
specified date part for a date or timestamp. |
Any valid date |
|
NEXT_DAY (date_or_time_expr
, dow_string) |
Returns the date of the first specified DOW (day of week) that
occurs after the input date. |
Any valid date |
|
PREVIOUS_DAY
(date_or_time_expr , dow_string) |
Returns the date of the first specified DOW (day of week) that
occurs before the input date. |
Any valid date |
|
DATEADD ( date_or_time_part,
value, date_or_time_expr) |
Adds the specified value for the specified date or time part to a
date, time, or timestamp. |
|
|
DATEDIFF (
date_or_time_part, date_or_time_expr1, date_or_time_expr2) |
Calculates the difference between two date, time, or timestamp
expressions based on the date or time part requested. |
Returns an integer representing the number of units (seconds,
days, etc.) |
|
DATE_PART (date_or_time_part, date_or_time_expr ) |
Extracts the specified date or
time part from a date, time, or timestamp. |
|
|
EXTRACT (date_or_time_part,
FROM date_or_time_expr) |
Extracts the specified date or time part from a date, time, or
timestamp. |
|
|
ADD_MONTHS
( date_or_timestamp_expr, num_months_expr) |
Adds or subtracts a specified number of months to a date or
timestamp, preserving the end-of-month information. |
The data type of the returned value is the same as the data type
of the first parameter. |
|
MONTHS_BETWEEN (date_expr1 , date_expr2) |
Returns the number of months
between two DATE or TIMESTAMP values. |
|
|
DATE_TRUNC
(date_or_time_part, date_or_time_expr) |
Truncates a DATE, TIME, or TIMESTAMP to the specified precision. |
The returned value is the same type as the input value. |
|
TIME_SLICE
(date_or_time_expr, slice_length , date_or_time_part
[ , start_or_end ]) |
Calculates the beginning or end of a “slice” of time, where the
length of the slice is a multiple of a standard unit of time (minute, hour,
day, etc.). |
|
|
DATE_FROM_PARTS( year, month, day) |
Creates a date from individual numeric components that represent
the year, month, and day of the month. |
|
|
TIME_FROM_PARTS (hour, minute, second [, nanoseconds]) |
Creates a time from individual numeric components. |
|
|
TIMESTAMP_FROM_PARTS ( year, month, day, hour, minute, second [, nanosecond
] [, time_zone ] ) OR (date_expr, time_expr) |
Creates a timestamp from individual numeric components. If no time
zone is in effect, the function can be used to create a timestamp from a date
expression and a time expression. |
|
Function SECOND()
--Example of the Function SECOND (DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, SECOND(T);
Results :
Function MINUTE()
--Example of the Function MINUTE (DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, MINUTE(T);
Results :
Function HOUR()
--Example of the Function HOUR (DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, HOUR(T);
Results :
Function DAYNAME()
--Example of the Function DAYNAME (DAY, DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, DAYNAME(T), DAYNAME(TO_DATE('2021-05-22'));
Results :
Function DAY ()
--Example of the Function DAY (DAY,DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, DAY(T), DAY(TO_DATE('2021-05-22'));
Function DAYOFMONTH()
--Example of the Function DAYOFMONTH(T) (DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, DAYOFMONTH(T), DAYOFMONTH(TO_DATE('2021-05-22'));
Results :
Function DAYOFYEAR()
--Example of the Function DAYOFYEAR(T) (DATEPART in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, DAYOFYEAR(T), DAYOFYEAR(TO_DATE('2021-05-22'));
results:
The DAYOFYEAR() is an alternative for the usage of the DATE_PART() (or EXTRACT) function.
Function WEEK()
--Example of the Function Week (and ISO)
SELECT CURRENT_TIMESTAMP() AS T,
WEEK(T),
WEEK(TO_DATE('2021-05-22')),
WEEKISO(T),
YEAROFWEEK(T),
YEAROFWEEKISO(T);
results :
The WEEK() Function is the same as WEEKOFYEAR() and is an alternative for the usage of the DATE_PART() (or EXTRACT) function.
Function WEEKISO()
SELECT CURRENT_TIMESTAMP() AS T,
WEEKISO(T);
Results :
The WEEKISO() Function is the same as WEEK() except it uses ISO Semantics. It is an alternative for the usage of the DATE_PART() (or EXTRACT) function.
Function YEAROFWEEK()
SELECT CURRENT_TIMESTAMP() AS T,
YEAROFWEEK(T);
Function YEAROFWEEKISO()
SELECT CURRENT_TIMESTAMP() AS T,
YEAROFWEEKISO(T);
Results :
The YEAROFWEEKISO() Function is the same as YEAROFWEEK() except it uses ISO Semantics. It is an alternative for the usage of the DATE_PART() (or EXTRACT) function.
Function MONTHNAME()
--Example of the Function MONTHNAME (MONTH() in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, MONTHNAME(T), MONTHNAME(TO_DATE('2021-05-22'));
Results :
Function QUARTER()
--QUARTRER (DATEPART() in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, YEAR(T), YEAR(TO_DATE('2021-05-22'));
Results :
It is an alternative for the usage of the DATE_PART() (or EXTRACT) function.
Function YEAR()
--Year (YEAR() in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T, YEAR(T), YEAR(TO_DATE('2021-05-22'));
Results :
Function LAST_DAY()
- month
- week (no weekiso)
- quarter
- year
--LAST_DAY (Custom logic and EOMONTH() in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T,
LAST_DAY(T),
LAST_DAY(TO_DATE('2021-05-22')),
LAST_DAY(T, 'WEEK'),
LAST_DAY(T, 'QUARTER'),
LAST_DAY(T, 'MONTH'),
LAST_DAY(T, 'YEAR');
Results :
Function NEXT_DAY()
- sunday.
- monday.
- tuesday.
- etc.
--NEXT_DAY (DATEADD in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T,
NEXT_DAY(T, 'Sunday' ),
NEXT_DAY(TO_DATE('2021-05-22'), 'Monday'),
NEXT_DAY(T, 'Tuesday'),
NEXT_DAY(T, 'we'),
NEXT_DAY(T, 'Thursday'),
NEXT_DAY(T, 'fri'),
NEXT_DAY(T, 'Saturday');
Results in :
Function PREVIOUS_DAY ()
- sunday.
- monday.
- tuesday.
- etc.
--PREVIOUS_DAY (DATEADD in SQL SERVER/ SQL Database)
SELECT CURRENT_TIMESTAMP() AS T,
PREVIOUS_DAY(T, 'Sunday' ),
PREVIOUS_DAY(TO_DATE('2021-05-22'), 'Monday'),
PREVIOUS_DAY(T, 'Tuesday'),
PREVIOUS_DAY(T, 'we'),
PREVIOUS_DAY(T, 'Thursday'),
PREVIOUS_DAY(T, 'Friday'),
PREVIOUS_DAY(T, 'Saturday');
Results :
Function DATEADD ()
- a date or time part. This is the unit you would like to add. Valid arguments are year, month, day, week, quarter, hour, minute, second, millisecond, microsecond and nanosecond.
- a value. The number of units you would like to add.
- a date or a time expression.
--Example of the Function DATEADD (DATEADD in SQL SERVER/ SQL Database)
--Aliasses : TIMEADD, TIMESATMPADD
SELECT
CURRENT_TIMESTAMP() AS T,
DATEADD(nanosecond,1,T), DATEADD(NS,1,T), DATEADD(ns,1,T),
DATEADD(microsecond,1,T), DATEADD(usec,1,T), DATEADD(US,1,T),
DATEADD(millisecond,1,T), DATEADD(MS,1,T),
DATEADD(second,1,T), DATEADD(SEC,1,T), DATEADD(SECONDS,1,T),
DATEADD(minute,1,T), DATEADD(Mi,1,T), DATEADD(min,1,T),
DATEADD(hour,1,T), DATEADD(HH,1,T),
DATEADD(day,1,T), DATEADD(dd,1,T),
DATEADD(week,1,T), DATEADD(wk,1,T),
DATEADD(month,1,T), DATEADD(month,1,T),
DATEADD(month,1,T), DATEADD(month,1,T),
DATEADD(quarter,1,T), DATEADD(qtr,1,T),
DATEADD(YEAR,1,T), DATEADD(YY,1,T), DATEADD(YYY,1,T), DATEADD(YYYY,1,T), DATEADD(Years,1,T),
DATEADD(DAY,1,T),
DATEADD(WEEK,1, T),
DATEADD(MONTH,1, T),
DATEADD(YEAR,1, T);
Function DATEDIFF ()
- a date or time part. This is the unit of time in which you would like the difference.
- a date or a time expression (1).
- a date or a time expression (2).
- year
- month
- day
- week
- quarter
- hour
- minute
- second
- nanosecond
--Example of the Function DATEDIFF (DATEDIFF in SQL SERVER/ SQL Database)
--Aliasses : TIMEDIFF, TIMESTAMPDIFF
SELECT
DATEDIFF(nanosecond, column1, column2), DATEDIFF(NS,column1, column2),
DATEDIFF(ns,column1, column2),
DATEDIFF(microsecond, column1, column2), DATEDIFF(usec,column1, column2),
DATEDIFF(US,column1, column2),
DATEDIFF(millisecond,column1, column2), DATEDIFF(MS,column1, column2),
DATEDIFF(second,column1, column2), DATEDIFF(SEC,column1, column2),
DATEDIFF(SECONDS,column1, column2),
DATEDIFF(minute,column1, column2), DATEDIFF(Mi,column1, column2),
DATEDIFF(min,column1, column2),
DATEDIFF(hour,column1, column2), DATEDIFF(HH,column1, column2),
DATEDIFF(day,column1, column2), DATEDIFF(dd,column1, column2),
DATEDIFF(week,column1, column2), DATEDIFF(wk,column1, column2),
DATEDIFF(month,column1, column2), DATEDIFF(month,column1, column2),
DATEDIFF(quarter,column1, column2), DATEDIFF(qtr,column1, column2),
DATEDIFF(YEAR,column1, column2), DATEDIFF(YY,column1, column2),
DATEDIFF(YYY,column1, column2), DATEDIFF(YYYY,column1, column2),
DATEDIFF(Years,column1, column2)
FROM VALUES (TO_DATE('2020-07-17'), CURRENT_TIMESTAMP());
Results (exported to a table) :
Function DATE_PART ()
- a date or time part.
- a date or a time expression.
- year
- month
- day
- dayofweek
- dayofweekiso
- dayofyear
- week
- weekiso
- quarter
- yearofweek
- yearofweekiso
- hour
- minute
- second
- nanosecond
- epoch_second
- epoch_millisecond
- epoch_microsecond
- epoch_nanosecond
- timezone_hour
- timezone_minute
--Example of the Function DATEPART (DATEPART in SQL SERVER/ SQL Database)
SELECT
CURRENT_TIMESTAMP() AS T,
DATE_PART(epoch, T), DATE_PART(epoch_seconds,T),
DATE_PART(epoch_microsecond,T), DATE_PART(epoch_microseconds,T),
DATE_PART(epoch_nanosecond,T), DATE_PART(epoch_nanoseconds,T),
-- DATE_PART(microsecond,T), DATE_PART(usec,T), DATE_PART(US,T),
-- DATE_PART(millisecond,T), DATE_PART(MS,T),
DATE_PART(second,T), DATE_PART(SEC,T), DATE_PART(SECONDS,T),
DATE_PART(minute,T), DATE_PART(Mi,T), DATE_PART(min,T),
DATE_PART(hour,T), DATE_PART(HH,T),
DATE_PART(day,T), DATE_PART(dd,T),
DATE_PART(week,T), DATE_PART(wk,T),
DATE_PART(month,T), DATE_PART(month,T),
DATE_PART(quarter,T), DATE_PART(qtr,T),
DATE_PART(YEAR,T), DATE_PART(YY,T), DATE_PART(YYY,T), DATE_PART(YYYY,T), DATE_PART(Years,T);
Results (exported to a table) :
Function EXTRACT ()
- a date or time part. This is the unit you would like to add. Valid arguments are year, month, day, week, quarter, hour, minute, second, millisecond, microsecond and nanosecond.
- a date or a time expression.
--Example of the Function EXTRACT (DATEPART in SQL SERVER/ SQL Database)
SELECT
CURRENT_TIMESTAMP() AS T,
EXTRACT(nanosecond FROM T), EXTRACT(NS FROM T), EXTRACT(ns FROM T),
EXTRACT(epoch FROM T), DATE_PART(epoch_seconds FROM T),
EXTRACT(epoch_microsecond FROM T), EXTRACT(epoch_microseconds FROM T),
EXTRACT(epoch_nanosecond FROM T), EXTRACT(epoch_nanoseconds FROM T),
-- EXTRACT(microsecond FROM T), EXTRACT(usec FROM T), EXTRACT(US FROM T),
-- EXTRACT(millisecond FROM T), EXTRACT(MS FROM T),
EXTRACT(second FROM T), EXTRACT(SEC FROM T), EXTRACT(SECONDS FROM T),
EXTRACT(minute FROM T), EXTRACT(Mi FROM T), EXTRACT(min FROM T),
EXTRACT(hour FROM T), EXTRACT(HH FROM T),
EXTRACT(day FROM T), EXTRACT(dd FROM T),
EXTRACT(week FROM T), EXTRACT(wk FROM T),
EXTRACT(month FROM T), EXTRACT(mon FROM T),
EXTRACT(quarter FROM T), EXTRACT(qtr FROM T),
EXTRACT(YEAR FROM T), EXTRACT(YY FROM T), EXTRACT(YYY FROM T),
EXTRACT(YYYY FROM T), EXTRACT(Years FROM T);
Function ADD_MONTHS ()
- a date or a time expression.
- number of months.
--Example of the Function ADDMONTHS
SELECT
CURRENT_TIMESTAMP() AS T,
ADD_MONTHS(T,1),
ADD_MONTHS('2020-02-29'::date, 1);
Results in :
Function MONTHS_BETWEEN ()
- a date or a timestamp expression (1).
- a date or a timestamp expression (2).
--Example of the Function MONTHS_BETWEEN (DATEDIFF in SQL SERVER/ SQL Database)
SELECT
CURRENT_TIMESTAMP() AS T,
MONTHS_BETWEEN(T,TO_DATE('2020-07-17')),
DATEDIFF(month,T,TO_DATE('2020-07-17'));
Results in :
Function DATE_TRUNC ()
- a date or a time part.
- a date or a timestamp expression.
- year
- month
- day
- week
- quarter
- hour
- minute
- second
- nanosecond
-- Example of the Function DATE_TRUNC (CAST/CONVERT and custom logic in SQL SERVER/ SQL Database)
-- Aliasses TRUNC
SELECT
CURRENT_TIMESTAMP() AS T,
DATE_TRUNC(nanosecond, T), DATE_TRUNC(NS, T), DATE_TRUNC(ns, T),
DATE_TRUNC(microsecond, T), DATE_TRUNC(usec, T), DATE_TRUNC(US, T),
DATE_TRUNC(millisecond, T), DATE_TRUNC(MS, T),
DATE_TRUNC(second, T), DATE_TRUNC(SEC, T), DATE_TRUNC(SECONDS, T),
DATE_TRUNC(minute,T), DATE_TRUNC(MI,T), DATE_TRUNC(min,T),
DATE_TRUNC(hour, T), DATE_TRUNC(HH, T),
DATE_TRUNC(day, T), DATE_TRUNC(dd,T),
DATE_TRUNC(week, T), DATE_TRUNC(wk, T),
DATE_TRUNC(month, T), DATE_TRUNC(month, T),
DATE_TRUNC(quarter, T), DATE_TRUNC(qtr, T),
DATE_TRUNC(YEAR, T), DATE_TRUNC(YY, T), DATE_TRUNC(YYY, T),
DATE_TRUNC(YYYY, T), DATE_TRUNC(Years, T),
DATE_TRUNC(HOUR,T),
DATE_TRUNC(month,T);
Function TIME_SLICE ()
- a date or time expression
- The slice length
- the date or time part
- START or END
-- Example of the Function TIME_SLICE (? in SQL SERVER/ SQL Database)
SELECT
CURRENT_TIMESTAMP()::timestamp_ntz AS T,
TIME_SLICE(T, 4, 'MINUTE', 'START'),
TIME_SLICE(T, 4, 'MINUTE', 'END'),
TIME_SLICE(T, 4, 'HOUR', 'START'),
TIME_SLICE(T, 4, 'HOUR', 'END'),
TIME_SLICE(T, 4, 'DAY', 'START'),
TIME_SLICE(T, 4, 'DAY', 'END'),
TIME_SLICE(T, 4, 'WEEK', 'START'),
TIME_SLICE(T, 4, 'WEEK', 'END'),
TIME_SLICE(T, 4, 'QUARTER', 'START'),
TIME_SLICE(T, 4, 'QUARTER', 'END'),
TIME_SLICE(T, 4, 'YEAR', 'START'),
TIME_SLICE(T, 4, 'YEAR', 'END');
Results (exported to a table) :
Function DATE_FROM_PARTS()
- year
- month
- day
-- Example of the Date_From_Parts Functions (DATEFROMPARTS in SQL SERVER/ SQL Database)
SELECT
DATE_FROM_PARTS(2021,5,22),
DATE_FROM_PARTS(2021, 5, 100),
DATE_FROM_PARTS(2021, 1 + 24, 1),
DATE_FROM_PARTS(2021, -1, -1);
Results in :
Function TIME_FROM_PARTS ()
- hour
- minutes
- seconds
- nanoseconds
-- Example of the Time_From_Parts Functions (DATETIMEFROMPARTS, CAST in SQL SERVER/ SQL Database)
SELECT
TIME_FROM_PARTS(20, 20, 22,12323),
TIME_FROM_PARTS(20,1000,22,12323);
Results in :
Function TIMESTAMP_FROM_PARTS ()
- year
- month
- day
- hour
- minute
- second
- nanosecond (optional)
- timezone
- date expression
- time expression
-- Example of the Timestamp_From_Parts Functions (DATETIMEFROMPARTS in SQL SERVER/ SQL Database)
SELECT
TIMESTAMP_FROM_PARTS(2021,5,22, 20,20,22,12323),
TIMESTAMP_FROM_PARTS(2021,5,22,20,1000,22,12323),
TIMESTAMP_FROM_PARTS('2021-05-23', '12:00:00'),
TIMESTAMP_LTZ_FROM_PARTS(2021,5,22, 20,20,22,12323),
TIMESTAMP_LTZ_FROM_PARTS(2021,5,22,20,1000,22,12323),
TIMESTAMP_NTZ_FROM_PARTS(2021,5,22, 20,20,22,12323),
TIMESTAMP_NTZ_FROM_PARTS(2021,5,22,20,1000,22,12323),
TIMESTAMP_TZ_FROM_PARTS(2021,5,22, 20,20,22,12323),
TIMESTAMP_TZ_FROM_PARTS(2021,5,22,20,1000,22,12323),
TIMESTAMP_TZ_FROM_PARTS(2021,5,22,20,1000,22,12323, 'America/Los_Angeles');
Results (exported to a table) :
