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

Question

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 1

EF doesn't treat all datetimes as DATETIME2, it treats all out of bounds or uninitialized dates as datetime2. From what I understand using Nullable<DateTime> to represent uninitialized datetimes instead of DateTime should solve your problem.

Answer 2

This is the OP's Feedback.

No need to do any datatype change on the SQL table and Just 2 steps as below.

step 1 :

Change the datatype of the parameter on the SP to be like this:

@cdin_startunstufdate1 DateTime2=null, @cdin_startunstufdate2 DateTime2=null

Step 2 :

Sp call should be like this.

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

Original 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.

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();