Different datetime format returns wrong result set in EF Core and LINQ. DateTime.ParseExact does not work

Question

I have an ASP.NET Core MVC application with the culture set to en-GB using a date time format of dd/mm/yyyy.

I am using:

• .NET Core 2.1
• EF Core 2.2
• SQL Server 2019 (v15)
• The date column is of type datetime2

However for one of my queries I need the date to be of format yyyy-mm-dd when querying through EF Core.

var xyx = db.zyz
            .Where(x => x.date >= startdate && x.date<= enddate)
            ......

The above query fails to return the result set even if there is a record in table zyz in accordance to the condition.

This I think is happening only when startdate and enddate are the same.

E.g. let's say zyz has a record for date 2020-04-19

Query

   var dt = DateTime.ParseExact("20200419","yyyyMMdd",CultureInfo.InvariantCulture);

    var xyx = db.zyz
                .Where(x=>x.date >= dt && x.date<= dt)
                ......

This fails to fetch any rows. It returns 0 rows.

I think this is because of the date time format.

The date field in zyz in the db is of the format 2020-19-04 00:00:00.0000000

Two questions come to mind:

• Is this because of the datetime format that I am not getting a result? Does EF Core not take care of the formatting?
• When I use ParseExact and different means to parse the date to a format I want. It does not work. I always get the date in the format of dd/mm/yyyy.

What I have tried, to parse the date:

var dt = DateTime.ParseExact("20200415","yyyyMMdd",CultureInfo.InvariantCulture);

DateTime theTime = DateTime.ParseExact("20200415",
                                        "yyyyMMdd",
                                        CultureInfo.InvariantCulture,
                                        DateTimeStyles.None);

var dts = DateTime.ParseExact("20200415", "yyyyMMdd",new CultureInfo("en-ZA"));

The above queries always return date of format 19/04/2020 00:00:00

As I understand since datetime is just a datatype, shouldn't the query be independent of the datetime format and return data irrespective of the datetime format?

Answer 1

Hi guys the query itself was wrong.

What was wrong :

In case of same start date and end date :

var xyx = db.zyz
            .Where(x => x.date >= startdate && x.date<= enddate)
            ......

I was sending:

2020-04-19 00:00:00 for both

I had to send:

2020-04-19 00:00:00 - for start date 2020-04-19 11:59:00 - for end date

As pointed out by Ivan Stoev and Stefano Balzarotti in the comments.

Wrote this answer as it makes it clear that datetime is a type and does not have a format as pointed out by Jon Skeet in the comments.