String was not recognized as a valid DateTime, stored procedure empty parameter

Question

I wrote a stored procedure in SQL Server. I have a parameter of type smalldatetime. I want to send this parameter blank when I run it with LINQ. When I want to send it, I get this error.

String was not recognized as a valid DateTime.

How can I send the date format blank?

C#, LINQ;

  var query = ctx.onayListele(Convert.ToDateTime(dataList.olusturulmaTarihi)).ToList();

SQL:

ALTER PROCEDURE [dbo].[onayListele]
    @in_olusturmaTarihi smalldatetime = NULL
AS
BEGIN
    SELECT 
        Onay.onayID, alep.olusturulmaTarihi, TalepTuru.talepTuruAdi, 
        TalepDurumu.talepDurumuAciklamasi 
    FROM 
        Onay 
    WHERE
        (@var_olusturmaTarihi IS NULL OR 
         CONVERT(DATE, Talep.olusturulmaTarihi) = CONVERT(DATE, @var_olusturmaTarihi))
END

If you were to simply run "DateTime dt = Convert.ToDateTime(dataList.olusturulmaTarihi);" does this produce the same error? If so you need to figure out why that value is not seen as a valid DateTime. — SoronelHaetir, Jan 01 '18 at 07:37
Because dataList.collection is sent as dataList.olusturulmaTarihi = "". And I do not know how to send the datetime parameter blank? — unluleyla, Jan 01 '18 at 07:42
Possible duplicate of [Call Stored Procedure with a null parameter value with EntityFramework](https://stackoverflow.com/questions/29373595/call-stored-procedure-with-a-null-parameter-value-with-entityframework) — pmcilreavy, Jan 01 '18 at 07:54
All you need to do is change the `@in_olusturmaTarihi` data type to `Date` and remove the conversion of it in your stored procedure. — Zohar Peled, Jan 01 '18 at 08:47

score 0 · Accepted Answer · answered Jan 01 '18 at 09:32

At first, I thought you needed to change your stored procedure.
Now that I've read the question again, I've realized that the error message comes from the c# side, not from the stored procedure (that I still think you should change).

Attempting to convert a null or empty string to DateTime will result with the error in your question. To avoid that, you need to make sure the string can in fact be converted to DateTime before sending it to the stored procedure:

DateTime datetime;
DateTime? olusturulmaTarihi = null;
if(DateTime.TryParse(dataList.olusturulmaTarihi, out datetime))
{
    olusturulmaTarihi = (DateTime?)datetime;
}
var query = ctx.onayListele(olusturulmaTarihi).ToList();

This way, you will send null to the stored procedure if the string can't be parsed as DateTime, and avoid the error.

As to the stored procedure, I would suggest writing it like this instead:

ALTER PROCEDURE [dbo].[onayListele]
    @in_olusturmaTarihi date = NULL
AS
BEGIN
   SELECT  Onay.onayID, 
           alep.olusturulmaTarihi, 
           TalepTuru.talepTuruAdi, 
           TalepDurumu.talepDurumuAciklamasi 
   FROM Onay 
   WHERE @var_olusturmaTarihi IS NULL 
   OR CONVERT(date,Talep.olusturulmaTarihi) = @var_olusturmaTarihi
END

Please note that if you have an index on Talep.olusturulmaTarihi, this stored procedure will not be able to use it. In that case, you better use something like this instead:

ALTER PROCEDURE [dbo].[onayListele]
    @in_olusturmaTarihi date = NULL
AS
BEGIN
   SELECT  Onay.onayID, 
           alep.olusturulmaTarihi, 
           TalepTuru.talepTuruAdi, 
           TalepDurumu.talepDurumuAciklamasi 
   FROM Onay 
   WHERE @var_olusturmaTarihi IS NULL 
   OR 
   (
       Talep.olusturulmaTarihi >= CAST(@var_olusturmaTarihi as datetime) -- or whatever the data type of the column is
       AND Talep.olusturulmaTarihi < DATEADD(DAY, 1, CAST(@var_olusturmaTarihi as datetime)) -- or whatever the data type of the column is
   )
END

String was not recognized as a valid DateTime, stored procedure empty parameter

1 Answers1