Dapper - passing getdate as parameter

dapper

Question

i am using SQL Dapper and i want to pass parameter like DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1) from UI. how can i do this?

DynamicParameter takes key as string and value as object and i am passing the key as the parameter i specified in the query and value as the expressing that i get from ui and here is the error what i get Conversion failed when converting date and/or time from character string.

--Need Help.

Expert Answer

It isn't entirely clear to me which bit you want to send as a parameter, since:

 DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)

is not a parameter. Ultimately, you could bake that directly into the SQL, although you might want to whack it in a variable:

declare @when datetime = DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1);
-- some more sql here, at some point using @when

If you want to pass in just a date as a parameter, then perform the date code first:

DateTime when = // some C# here...
var data = conn.Query<Whatever>(sql, new { when, ... }).ToList();

It is never possibly to directly pass in a complex statement (such as DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)) as a single typed parameter. In normal usage, a parameter of type (say) datetime is given a single value, typically from a computed DateTime.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why