How to format date variable in c# to match stored date in SQL database

Question

I'm reading in a date from a textbox into a C# program and comparing to a date to entries in a SQL database. I can't get them to match, despite a SQL query of the date showing there are entries for that date.

I've tried saving to a variable with .Date to try and strip off the hh:mm:ss, which had no results. I've attempted DateTime.ParseExact to change the format to match the database.

if (!System.String.IsNullOrEmpty(searchDate))
{
      CultureInfo culture = new CultureInfo("en-US");
      DateTime completeDate = DateTime.ParseExact(searchDate, "M/d/yyyy", culture);
      requests = requests.Where(x => x.CompletionDate == completeDate);
}

I expect to find a match for two records in the database when I search for 1/28/2019. I'm getting no results. I've tried other entries and also not found any matches. No error messages are being generated.

Do you store also the Time part in the database column _CompletionDate_? — Steve, Jul 29 '19 at 22:20
Yes, it does, it has 00:00:00.000 - is there a way to add that to my variable or strip it off the database entry when I compare the two? — trw0605, Jul 29 '19 at 22:26
Ok but is there a way to perform it within the Razor statement that is accessing the database? — trw0605, Jul 29 '19 at 22:33
To be sure to not miss anything due to time parts I would use _requests.Where(x => x.CompletionDate >= completeDate && x.CompletionDate < completeDate.AddDays(1));_ — Steve, Jul 29 '19 at 22:33
What is a sample `searchDate` string that you're passing through? What is the value of `completeDate` if you inspect it? What is the exact value in the database that you are expecting the LINQ query to find? — Jonathan, Jul 29 '19 at 22:41
I got it to work by creating a second variable and using completeDateNext = completeDate.AddDays(1), then searching between those two dates. Linq doesn't support AddDays directly. But thanks much - it works now! — trw0605, Jul 29 '19 at 22:43
If you are using EntityFramework then you can use [EntityFunctions](https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.entityfunctions?view=netframework-4.8) to [TruncateTime](https://learn.microsoft.com/en-us/dotnet/api/system.data.objects.entityfunctions.truncatetime?view=netframework-4.8#System_Data_Objects_EntityFunctions_TruncateTime_System_Nullable_System_DateTime__) and compare it with your date. This is not a good solution though as it will not work with indexes because it is translated into TSQL `convert` statements. Range search is better here. — fenixil, Jul 30 '19 at 02:02

score 0 · Answer 1 · answered Jul 29 '19 at 22:29

If you simply compare two DateTime objects, they will consider not only date but also time.

To compare just the date, you can use DateTime.Date property.

var date1 = new DateTime(2019, 7, 29, 15, 23, 44);
var date2 = new DateTime(2019, 7, 29);

Console.WriteLine(date1 == date2);
Console.WriteLine(date1.Date == date2.Date);

The first will return false, the second will return true in this case.

score 0 · Answer 2 · answered Jul 29 '19 at 23:20

0

The issue is time isn't a factor your comparison and shouldn't be included. You need to explicitly compare date parts only...

requests = requests.Where(x => x.CompletionDate.Date == completeDate.Date);

answered Jul 29 '19 at 23:20

drooksy

284
2
8

score -1 · Answer 3 · edited Jul 30 '19 at 02:41

-1

CultureInfo culture = new CultureInfo("en-US");

DateTime completeDate = DateTime.ParseExact(searchDate, "M/d/yyyy", culture);

completeDate = 1/28/2019 12:00:00 AM

if the date stored in db is 1/28/2019 it wont match.

edited Jul 30 '19 at 02:41

realr

3,652
6
23
34

answered Jul 29 '19 at 22:42

R. Salha

1
1

How to format date variable in c# to match stored date in SQL database

3 Answers3