How to dynamically pass in a date_or_time_part for DateDiff?

user_2487496 0
Viewed 60

I have a column with strings such as "month" and "year" in it, but when I pass that into the first argument for datediff it fails saying:

['COLUMN_NAME'] is not a valid date/time component for function DATEDIFF.

How can I pass in column_name as the first argument to datediff as a date_or_time_part so that it can dynamically set the time unit?

snowflake-cloud-data-platform date sql
3 Answers

date_or_time_part must be one of the values listed in Supported Date and Time Parts (e.g. month). The value can be a string literal or can be unquoted (e.g. 'month' or month).

You can use a CASE expression

case when mycolumn = 'month' then DATEDIFF(month,...)
    when mycolumn = 'year' then DATEDIFF(year,...)
end diff
user_62199790

You can use the SWITCH statement form of CASE thus you just need to branch the options you want, and matching one will be used.

SELECT 
    date_from
    ,date_to
    ,part
    ,case part
       when 'month' then datediff('month', date_from, date_to) 
       when 'day' then datediff('day', date_from, date_to) 
       when 'hour' then datediff('hour', date_from, date_to) 
   end AS part_value
FROM VALUES 
('2021-12-21 12:30:00', '2022-01-30 12:30:00', 'month'),
('2022-01-21 12:30:00', '2022-01-30 12:30:00', 'day'),
('2022-01-30 10:30:00', '2022-01-30 12:30:00', 'hour')
v(date_from, date_to, part);
DATE_FROM DATE_TO PART PART_VALUE
2021-12-21 12:30:00 2022-01-30 12:30:00 month 1
2022-01-21 12:30:00 2022-01-30 12:30:00 day 9
2022-01-30 10:30:00 2022-01-30 12:30:00 hour 2
user_439920

Try this..

Create table tblDateDifference(diffby  nvarchar(50),startdate  Date ,endDate  Date )

Insert into tblDateDifference(diffby,startdate,endDate) values('month','2022-01-29','2023-05-29')
Insert into tblDateDifference(diffby,startdate,endDate) values('year','2022-06-29','2023-01-29');
Insert into tblDateDifference(diffby,startdate,endDate) values('month','2020-01-19','2023-01-29');
Insert into tblDateDifference(diffby,startdate,endDate) values('year','2022-11-29','2023-01-29');
Insert into tblDateDifference(diffby,startdate,endDate) values('Days','2022-01-29','2023-01-29');

Select diffby,case when diffby='month' then dateDiff(month,startDate,endDate) 
when diffby='year' then  dateDiff(year,startDate,endDate) 
else  dateDiff(day,startDate,endDate) end Datedifference,startdate,enddate
 from tblDateDifference
user_91165800
Related