Oracle Passthrough to Snowflake Passthrough

[D_Z_]

i am having some issues converting some passthrough code from Oracle to Snowflake for date and time intervals.

The original code form oracle passthrough is this:

and (( o.order_timestamp >= trunc(to_date(%str(%'&Startdate%')),'ddmonyyyy') and 
       o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy'),'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss') or
       o.order_timestamp >= trunc(to_date(%str(%'&Startdate%')),'ddmonyyyy')-364 and  
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy')-364,'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss')))

i took of the trunc statements like this:

and (( o.order_timestamp >= (to_date(%str(%'&Startdate%')),'ddmonyyyy') and 
       o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy'),'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss') or
       o.order_timestamp >= (to_date(%str(%'&Startdate%')),'ddmonyyyy')-364 and  
o.order_timestamp < to_date(to_char(to_date(%str(%'&Startdate%'),'ddmonyyyy')-364,'yyyymmdd')||decode(%str(%'&interval%')||'00','240000','235959',%str(%'&interval%')||'00'),'yyyymmddhh24miss

 The parameter code looks like this:

call symput("Startdate",put("&START_DATE"d,yymmdd10.));
  call symput("Begindate",put("&START_DATE"d,yymmdd10.));
  call symput("Enddate",put("&START_DATE"d,yymmdd10.));

When i ran the project with the modified date code, i received no errors and a blank page.

From there i tried my simple date code just to see if was that part of the code was truly the issue:

and o.order_timestamp >= dateadd(dd,-0,%str(%')&Startdate%str(%'))
  and o.order_timestamp <= dateadd(dd,+1,%str(%')&Startdate%str(%'))

When i replaced the date code with this it worked perfectly and i was able to see all my orders for the previous day.  The issue is those two DECODE statements are required here.  There are several other pieces to this code and i am doing the conversion for them but i can't figure out the right syntax to make it work.  I don't likely care what i use in the end as long as it gives me the interval data.

Can someone please help with the syntax... i am been working on this for 3 hours now and am just beating my head.

Thanks,

Dean

[Patrick]

In Oracle the data types Date and Timestamp both store DateTime values. The only difference is that Timestamp can also store fractional seconds.

The TRUNC() in your Oracle SQL simply aligns the Date value to the beginning of the day - but it remains a DateTime value (and you could also just have used a date string only for you to_date() function to get the same result).

Based on the snowflake documentation here the data type Date is more like a SAS Date value and Timestamp data types are more like SAS DateTime values. Furthermore the documentation here tells us that the To_Date() function in snowflake will only use the date portion of a DateTime string for conversion. ...So I guess you'll end up comparing a snowflake Timestamp with a snowflake Date value and the result is similar to comparing a SAS DateTime to a SAS Date value. 

There is quite good documentation available:

Using Dates and Timestamps  

Date & Time Functions

May be the least code-change as compared to your Oracle SQL would be something like:

to_date(o.order_timestamp) >= (to_date(%str(%'&Startdate%')),'ddmonyyyy')

 Alternatively something like below should also work.

o.order_timestamp >= date_trunc('DAY', TO_TIMESTAMP_NTZ(%str(%'&Startdate%')),'ddmonyyyy'))