This question already has answers here:
send datepart as parameter from a table to DATEADD function in sql server (2 answers)
Closed 6 days ago.
I have a data set like this:
CREATE TABLE #Dummy
(
Latest_Appointment_Date date,
Interval varchar(255),
Increment int
);
INSERT INTO #Dummy (Latest_Appointment_Date, Interval, Increment)
VALUES ('2023-11-10', 'week', 2),
('2023-11-23', 'month', 1);
Where the interval and increment columns denote how much I want to increment the date by (using DATEADD
, for example). I can't figure out how to use the column values as the parameters of my DATEADD
.
When I do
SELECT
*, DATEADD(Interval, Increment, Latest_Appointment_Date)
FROM #Dummy
I get the error
'Interval' is not a recognized dateadd option
Any insight would be great. Thank you. I'm using SQL Server.
2条答案
按热度按时间tjjdgumg1#
T-SQL's
DATEADD
is not a "real" function , it's actually a language-feature (like a statement or control-structure) and the first parameter (datepart
) accepts keyword arguments, not value arguments, so you cannot parameterize it using a@variable
nor column expression......instead, you'll need to use a
CASE
expression:aiazj4mn2#
My answer is basically what Dai said. Here's an inline table valued function you can use to abstract away that logic so you can reuse it elsewhere if you need (I copied all the various possible inputs to the proc from the DATEADD documentation)
Example Usage