How to change date format in Linq query

[Kiboko2]

Hi All,

I'm trying to query a MS SQL 2016 db table using the following linq expression.

return _context.RVOPlantReadings
.Where(r => r.ReadingTimeStamp.Year.Equals(readingDate.Year)
&& r.ReadingTimeStamp.Month.Equals(readingDate.Month)
&& r.ReadingTimeStamp.Day.Equals(readingDate.Day)
&& r.PlantName.Equals(systemName) && r.companyId.Equals(companyID))
.FirstOrDefault();
readingDate is passed into the query in the format dd-MM-yyyy
However the database is configured to use en-US format so when I pass in the date 04-01-2016 (fourth of Jan 2016), the query returns the entry with the ReadingTimeStamp of 01-04-2016 (first of April 2016). I can't format the date using formatting such as readingDate.toString("MM-dd-yyyy") to try and turn the date I'm passing in to US format as this will throw an error.
readingDate is a DateTime object.
Running 'dbcc useroptions' on the SQL server returns
language = us_english
dateformat = mdy

When I asked the provider (Smarter ASP.NET) to change the language/date format on the server they replied

Dear Customer,

We providing hosting service all over the world, we cant change the shared server global setting. It is use US format by default.

For the database data, it is always used for your web applications, so update your application's web.config file would control the data time format.

Adding a <globalization> node to the web.config file obviously has no effect at all. Dates shown on razor pages can be / are formatted to en-GB format.

Can anyone suggest anything please.

Continue reading...