Skip to content
Advertisement

How to dynamically pass in a date_or_time_part for DateDiff?

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?

Advertisement

Answer

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 contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement