0

I'm getting the error $exception {"An error occurred while reading from the store provider's data reader. See the inner exception for details."} System.Data.Entity.Core.EntityCommandExecutionException

the inner exception says InnerException {"Conversion failed when converting date and/or time from character string."} System.Exception {System.Data.SqlClient.SqlException}

The URL I'm using is 2466:api/fullmessage/company/WaterMeter/2017-03-01/2018-04-01

this is the code i'm using:

public List<FullMessage> GetByCompanyTypeBetweenDays(string id, string type, string day1, string day2)
    {
        DateTime Day1;
        DateTime Day2;
        if (!DateTime.TryParse(day1, out Day1))
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        }
        if (!DateTime.TryParse(day2, out Day2))
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        };
        using (IoTdatabaseEntities entities = new IoTdatabaseEntities())
        {
            return entities.Database.SqlQuery<FullMessage>(@"SELECT *
            FROM [Messages] join [Devices] on [Devices].[DeviceID] = [Messages].[DeviceID]
            Where [devices].[company] = '@company' and [messages].[type] = '@type' and
            [Messages].[MessageSentDateTime] between '@FirstDay' AND '@LastDay'
            Order by [Messages].[MessageSentDateTime]
            ", new SqlParameter("company", id), new SqlParameter("type", type), new SqlParameter("FirstDay", Day1.ToString("yyyyMMdd")),new SqlParameter("LastDay",Day2.ToString("yyyyMMdd"))).ToList();
        }
    }

I tried to remove the ToString("yyyyMMdd") method but this still gave me errors

public List<FullMessage> GetByCompanyTypeBetweenDays(string id, string type, string day1, string day2)
    {
        DateTime Day1;
        DateTime Day2;
        if (!DateTime.TryParse(day1, out Day1))
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        }
        if (!DateTime.TryParse(day2, out Day2))
        {
            throw new HttpResponseException(HttpStatusCode.BadRequest);
        };
        using (IoTdatabaseEntities entities = new IoTdatabaseEntities())
        {
            return entities.Database.SqlQuery<FullMessage>(@"SELECT *
        FROM [Messages] join [Devices] on [Devices].[DeviceID] = [Messages].[DeviceID]
        Where [devices].[company] = '@company' and [messages].[type] = '@type' and
        [Messages].[MessageSentDateTime] between '@FirstDay' AND '@LastDay'
        Order by [Messages].[MessageSentDateTime]
        ", new SqlParameter("company", id), new SqlParameter("type", type), new SqlParameter("FirstDay", Day1), new SqlParameter("LastDay", Day2)).ToList();
        }
    }

Can someone please tell me what I'm doing wrong. the query works when I execute it in the database.

in this post, they removed the quotes around the dates and this just doesn't return anything at all Handling date in SQL Server

Jelle Taal
  • 25
  • 1
  • 7

0 Answers0