Search code examples
sqlsql-serverdatedatetimedateadd

SQL Server: convert string 'n', 'hh', 'd' to global variables n, hh and d

We're using a very high level framework, called Instant Developer, where Date Parts are implemented as strings. Implementing an interface, I added to the library of my project the function DATEADD (datepart , number , date ) from SQL Server. But when I call it, the generated code is, for example, 

SELECT dateadd('n',90,CONVERT(datetime, DATEREQUEST+TIMEREQUEST))
FROM [Ergo].[dbo].[MANUTENZIONI]
where TICKETMANUTE = 17723

but SQL Server rises ax exception since the first parameter of dateadd() function is incorrect because it's not supposed to be a string but a global variable. Is there a way to convert it? Something like:

dateadd(case when 'n'='n' then n end, 90, CONVERT(datetime, DATEREQUEST+TIMEREQUEST))
Solution

  • Create a wrapper function which accepts varchar as a type of interval

    create function myDateadd(@type varchar(5), @amount int, @dt datetime)
returns datetime
as
begin
return case @type
       when 'n' then dateadd(n, @amount, @dt)
       when 'd' then dateadd(d, @amount, @dt)
       -- ...
       end;
end
go

select dbo.myDateadd('n',60,getdate());