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