Fares Ayyad Fares Ayyad - 3 months ago 102
C# Question

Passing DateTime parameter to stored procedure using EF(DB first)

I created a stored procedure in SQL Server 2012

ALTER proc [dbo].[select_alltypes]
@cdin_startunstufdate1 DateTime = null,
@cdin_startunstufdate2 DateTime = null
AS
BEGIN
SET NOCOUNT ON;

SELECT
0 as shipid,
i.cdin_cdindexid, p.pinv_PerformaInvID,
coalesce(i.cdin_serial, 0) as depno,
coalesce(convert(datetime, left(convert(nvarchar, i.cdin_startunstufdate, 120), 10), 120),'-') as deidate,
coalesce(i.cdin_goodsDesc, '-') as gooddesc,
coalesce(i.cdin_Customdeclar, '-') as custdec,
coalesce(i.cdin_NoofPackages, 0) as pkg,
coalesce(i.cdin_WT, 0) as wt,
coalesce(i.cdin_volumewt, 0) as vwt,
coalesce(i.cdin_MortgageAmount, 0) as lcamt,
coalesce(p.pinv_name, '-') as invno,
coalesce(p.pinv_TotalAmount, 0) as invamt,
p.pinv_Status, p.pinv_InvoiceProperty as prop,
coalesce(c.comp_name, '-') as custname,
coalesce(Comp_CompanyId, '-') as custid,
coalesce(c.comp_idcust, '-') as accpacno,
coalesce(t.Terr_Caption, '-') as Terr,
convert(nvarchar, '01', 2) as type
from
cdindex i
inner join
company c on i.cdin_CompanyId = c.Comp_CompanyId
inner join
Territories t on i.cdin_Secterr = t.Terr_TerritoryID
left outer join
PerformaInv p on i.cdin_cdindexid = p.pinv_CDIndexId
where
(cdin_deleted Is null And c.comp_deleted Is null
And t.Terr_Deleted Is null And p.pinv_deleted Is null)
and cdin_startunstufdate between @cdin_startunstufdate1 and @cdin_startunstufdate2
and (p.pinv_status in ('Draft', 'Posted') or pinv_status is null)
and (p.pinv_InvoiceProperty = '01' or p.pinv_InvoiceProperty is null )
end


I want to call this proc using EF like this:

var sp = db.select_alltypes(new DateTime (2016-07-01), new DateTime (2016-07-28)).ToList();


Every time I call it with passing parameters, the debugger shows a runtime error.

But if I call it without parameters, with altering the stored procedure to be without parameters and give it specified date:

var sp = db.select_alltypes().ToList();


It returns the expected parameter.

The error is:


Additional information: An error occurred while executing the command
definition. See the inner exception for details.

Error converting data type datetime2 to datetime

Answer

Entity framework handles all the dates as a Datetime2,

Problem :

If your fields in the database are Datetime,then that can be a problem.

Solution :

You have to change the cdindex table's cdin_startunstufdate column date field datatype to datetime2.

How to change the datetime to dDatetime2

Just go to your SQL table's datetime column and change it as shown below.

enter image description here

Update :

Sp call should be like this.

var sp = db.select_alltypes(DateTime.Parse("2016-07-01"),DateTime.Par‌​se("2016-
07-28")).To‌​List();