SNOWFLAKE conversion DATETIME 0

Question

I am aware that Snowflake is very similar to Oracle. I am doing a job that requires me to move some SQL Server code over into snowflake , however I can't seem to get these date functions converted over. In SSMS this SELECT DATEADD(YEAR,DATEDIFF(YEAR,0,'1912-01-01'),0) statement would return 1912-01-01 00:00:000. since the 0 indicates "The beginning of time" what would be Snowflakes equivalent to this 0 be and better yet how would you convert this to say Oracle code?

Thank you ahead of time!

Answer 1

Sharing few examples from Snowflake -

select date_trunc(year,'1912-01-05'::date);
+-------------------------------------+
| DATE_TRUNC(YEAR,'1912-01-05'::DATE) |
|-------------------------------------|
| 1912-01-01                          |
+-------------------------------------+

select date_trunc(year,'1912-10-05'::date);
+-------------------------------------+
| DATE_TRUNC(YEAR,'1912-10-05'::DATE) |
|-------------------------------------|
| 1912-01-01                          |
+-------------------------------------+

select date_trunc(year,'1912-12-01 10:00:000'::timestamp);
+----------------------------------------------------+
| DATE_TRUNC(YEAR,'1912-12-01 10:00:000'::TIMESTAMP) |
|----------------------------------------------------|
| 1912-01-01 00:00:00.000                            |
+----------------------------------------------------+

Answer 2

I am familiar with the method you are talking about. It was a workaround to make up for the fact that SQL Server did not have a function to truncate date/time.

In Snowflake, just use DATE_TRUNC( <date_or_time_part>, <date_or_time_expr> )

https://docs.snowflake.com/en/sql-reference/functions/date_trunc.html

select to_date('2015-05-08T23:39:20.123-07:00') as "DATE1",
       date_trunc('YEAR', "DATE1") as "TRUNCATED TO YEAR",
       date_trunc('MONTH', "DATE1") as "TRUNCATED TO MONTH",
       date_trunc('DAY', "DATE1") as "TRUNCATED TO DAY";
+------------+-------------------+--------------------+------------------+
| DATE1      | TRUNCATED TO YEAR | TRUNCATED TO MONTH | TRUNCATED TO DAY |
|------------+-------------------+--------------------+------------------|
| 2015-05-08 | 2015-01-01        | 2015-05-01         | 2015-05-08       |
+------------+-------------------+--------------------+------------------+


select to_timestamp('2015-05-08T23:39:20.123-07:00') as "TIMESTAMP1",
       date_trunc('HOUR', "TIMESTAMP1") as "TRUNCATED TO HOUR",
       date_trunc('MINUTE', "TIMESTAMP1") as "TRUNCATED TO MINUTE",
       date_trunc('SECOND', "TIMESTAMP1") as "TRUNCATED TO SECOND";
+-------------------------+-------------------------+-------------------------+-------------------------+
| TIMESTAMP1              | TRUNCATED TO HOUR       | TRUNCATED TO MINUTE     | TRUNCATED TO SECOND     |
|-------------------------+-------------------------+-------------------------+-------------------------|
| 2015-05-08 23:39:20.123 | 2015-05-08 23:00:00.000 | 2015-05-08 23:39:00.000 | 2015-05-08 23:39:20.000 |
+-------------------------+-------------------------+-------------------------+-------------------------+

Answer 3

Snowflake and Oracle both have a date truncate function

Snowflake:

DATE_TRUNC has many supported periods it can truncate to:

SELECT '1921-12-23'::date as d
    ,DATE_TRUNC('year', d) as d_trunc_to_year;

gives:

D	D_TRUNC_TO_YEAR
1921-12-23	1921-01-01

Oracle:

Oracle has TRUNC

Which there example looks to solve a rather similar way:

SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR')
  "New Year" FROM DUAL;
 
New Year
---------
01-JAN-92