How do i subtract time from datetime in snowflake?-CodePudding

pdt.startTime is datetime

s_first.FromTimeOfDay is a time

I want to subtract the time drom the datetime. When i run the code below, Snowflake gives me this error invalid type [CAST(S_FIRST.FROMTIMEOFDAY AS TIMESTAMP_NTZ(9))] for parameter 'TO_TIMESTAMP_NTZ'

select (pdt.StartTime - (SELECT s_first.FromTimeOfDay::datetime FROM Shift s_first))
from RAW_CPMS_AAR.POWERBI_DowntimeTable AS PDT

When i try this:

select (pdt.StartTime::TIMESTAMP_NTZ(9) - (SELECT s_first.FromTimeOfDay::TIMESTAMP_NTZ(9) FROM Shift s_first))
from RAW_CPMS_AAR.POWERBI_DowntimeTable AS PDT

I get more or less the same error: invalid type [CAST(S_FIRST.FROMTIMEOFDAY AS TIMESTAMP_NTZ(9))] for parameter 'TO_TIMESTAMP_NTZ'

How do I convert the time into a datetime format so that I can subtract the two. It doesnt seem to me that there is a clear way to convert time into datetime in snowflake.

CodePudding user response：

Is this what you're after?

select current_timestamp() as sample_timestamp
, time(sample_timestamp) as sample_time
, date(sample_timestamp) as sample_date;

CodePudding user response：

A user pointed me in the right direction. i didnt realize i could use "dateadd" to also subtract time.

dateadd(HOUR, - (HOUR(current_timestamp())), temp.DateTime)