How to call a stored procedure from MVC that requires datetimes

Question

I'm wanting to call this stored procedure with input parameters:

proc [dbo].[Invoice_GetHomePageInvoices] (

      @FinancialYearStartDate datetime = null
,      @FinancialYearEndDate datetime = null

Trouble with this is the way I usually call a stored proc from my code is:

return _db.Database.SqlQuery<HomePageInvoice>(string.Format("EXEC Invoice_GetHomePageInvoices @FinancialYearStartDate = '{0}', @FinancialYearEndDate = '{1}'", financialYear.StartDate.ToString(), financialYear.EndDate.ToString()));

So this isn't going to work because I've basically converted my datetimes to strings.

How the heck am I supposed to do this?

score 6 · Accepted Answer · edited May 23 '17 at 12:27

You should use sql parameters, basically like this:

var startDate = new SqlParameter("FinancialYearStartDate", dateTimeValueHere);
var endDate = new SqlParameter("FinancialYearEndDate", dateTimeValueHere);

return _db.Database.SqlQuery<HomePageInvoice>("Invoice_GetHomePageInvoices", startDate, endDate);

More info: How to use DbContext.Database.SqlQuery<TElement>(sql, params) with stored procedure? EF Code First CTP5

score 0 · Answer 2 · answered Apr 16 '12 at 22:16

convert the date to a string with specific format.
use SQL code to convert it back to an sql datetime object.

When using sql server (microsoft): http://msdn.microsoft.com/en-us/library/ms187928.aspx "convert(datetime, '{0}', 103)", financialYear.EndDate.ToString("MM/dd/yyyy")

edit: Ropstah's method is actually better, this is just the way I used to do it :P

How to call a stored procedure from MVC that requires datetimes

2 Answers2