How to dynamically pass in a date_or_time_part for DateDiff?-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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