How to compare only Date without Time in DateTime types in Linq to SQL with Entity Framework?

Question

Is there a way to compare two DateTime variables in Linq2Sql but to disregard the Time part.

The app stores items in the DB and adds a published date. I want to keep the exact time but still be able to pull by the date itself.

I want to compare 12/3/89 12:43:34 and 12/3/89 11:22:12 and have it disregard the actual time of day so both of these are considered the same.

I guess I can set all the times of day to 00:00:00 before I compare but I actually do want to know the time of day I just also want to be able to compare by date only.

I found some code that has the same issue and they compare the year, month and day separately. Is there a better way to do this?

Answer 1

try using the Date property on the DateTime Object...

if(dtOne.Date == dtTwo.Date)
    ....

Answer 2

For a true comparison, you can use:

dateTime1.Date.CompareTo(dateTime2.Date);

Answer 3

This is how I do this in order to work with LINQ.

DateTime date_time_to_compare = DateTime.Now;
//Compare only date parts
context.YourObject.FirstOrDefault(r =>
                EntityFunctions.TruncateTime(r.date) == EntityFunctions.TruncateTime(date_to_compare));

If you only use dtOne.Date == dtTwo.Date it wont work with LINQ (Error: The specified type member 'Date' is not supported in LINQ to Entities)

Answer 4

If you're using Entity Framework < v6.0, then use EntityFunctions.TruncateTime If you're using Entity Framework >= v6.0, then use DbFunctions.TruncateTime

Use either (based on your EF version) around any DateTime class property you want to use inside your Linq query

Example

var list = db.Cars.Where(c=> DbFunctions.TruncateTime(c.CreatedDate) 
                                       >= DbFunctions.TruncateTime(DateTime.UtcNow));

Answer 5

DateTime dt1 = DateTime.Now.Date;
DateTime dt2 = Convert.ToDateTime(TextBox4.Text.Trim()).Date;
if (dt1 >= dt2)
{
    MessageBox.Show("Valid Date");
}
else
{
    MessageBox.Show("Invalid Date... Please Give Correct Date....");
}

Answer 6

DateTime? NextChoiceDate = new DateTime();
DateTIme? NextSwitchDate = new DateTime();
if(NextChoiceDate.Value.Date == NextSwitchDate.Value.Date)
{
Console.WriteLine("Equal");
}

You can use this if you are using nullable DateFields.

Answer 7

DateTime dt1=DateTime.ParseExact(date1,"dd-MM-yyyy",null);
DateTime dt2=DateTime.ParseExact(date2,"dd-MM-yyyy",null);

int cmp=dt1.CompareTo(dt2);

   if(cmp>0) {
       // date1 is greater means date1 is comes after date2
   } else if(cmp<0) {
       // date2 is greater means date1 is comes after date1
   } else {
       // date1 is same as date2
   }

Answer 8

DateTime econvertedDate = Convert.ToDateTime(end_date);
DateTime sconvertedDate = Convert.ToDateTime(start_date);

TimeSpan age = econvertedDate.Subtract(sconvertedDate);
Int32 diff = Convert.ToInt32(age.TotalDays);

The diff value represents the number of days for the age. If the value is negative the start date falls after the end date. This is a good check.

Answer 9

You can use Equals or CompareTo.

Equals: Returns a value indicating whether two DateTime instances have the same date and time value.

CompareTo Return Value:

1. Less than zero : If this instance is earlier than value.
2. Zero : If this instance is the same as value.
3. Greater than zero : If this instance is later than value.

DateTime is nullable:

DateTime? first = new DateTime(1992,02,02,20,50,1);
DateTime? second = new DateTime(1992, 02, 02, 20, 50, 2);

if (first.Value.Date.Equals(second.Value.Date))
{
    Console.WriteLine("Equal");
}

or

DateTime? first = new DateTime(1992,02,02,20,50,1);
DateTime? second = new DateTime(1992, 02, 02, 20, 50, 2);


var compare = first.Value.Date.CompareTo(second.Value.Date);

switch (compare)
{
    case 1:
        Console.WriteLine("this instance is later than value.");
        break;
    case 0:
        Console.WriteLine("this instance is the same as value.");
        break;
    default:
        Console.WriteLine("this instance is earlier than value.");
        break;
}

DateTime is not nullable:

DateTime first = new DateTime(1992,02,02,20,50,1);
DateTime second = new DateTime(1992, 02, 02, 20, 50, 2);

if (first.Date.Equals(second.Date))
{
    Console.WriteLine("Equal");
}

or

DateTime first = new DateTime(1992,02,02,20,50,1);
DateTime second = new DateTime(1992, 02, 02, 20, 50, 2);


var compare = first.Date.CompareTo(second.Date);

switch (compare)
{
    case 1:
        Console.WriteLine("this instance is later than value.");
        break;
    case 0:
        Console.WriteLine("this instance is the same as value.");
        break;
    default:
        Console.WriteLine("this instance is earlier than value.");
        break;
}

Answer 10

You can try

if(dtOne.Year == dtTwo.Year && dtOne.Month == dtTwo.Month && dtOne.Day == dtTwo.Day)
  ....

Answer 11

In your join or where clause, use the Date property of the column. Behind the scenes, this executes a CONVERT(DATE, <expression>) operation. This should allow you to compare dates without the time.

Answer 12

In .NET 5:

To compare date without time you must use EF.Functions.DateDiffDay() otherwise you will be comparing in code and this means you are probably pulling way more data from the DB than you need to.

.Where(x => EF.Functions.DateDiffDay(x.ReceiptDate, value) == 0);

Answer 13

        int o1 = date1.IndexOf("-");
        int o2 = date1.IndexOf("-",o1 + 1);
        string str11 = date1.Substring(0,o1);
        string str12 = date1.Substring(o1 + 1, o2 - o1 - 1);
        string str13 = date1.Substring(o2 + 1);

        int o21 = date2.IndexOf("-");
        int o22 = date2.IndexOf("-", o1 + 1);
        string str21 = date2.Substring(0, o1);
        string str22 = date2.Substring(o1 + 1, o2 - o1 - 1);
        string str23 = date2.Substring(o2 + 1);

        if (Convert.ToInt32(str11) > Convert.ToInt32(str21))
        {
        }
        else if (Convert.ToInt32(str12) > Convert.ToInt32(str22))
        {
        }
        else if (Convert.ToInt32(str12) == Convert.ToInt32(str22) && Convert.ToInt32(str13) > Convert.ToInt32(str23))
        {
        }