1

I wanted to compare the datetime which is in this format "7/20/2008" with the ones in the database which is in format "7/20/2008 7:14:53 AM".

I tried using "like" clause but it did not work beacuse the "like" clause uses only string and the one which I am using is date time format.

Can anyone tell how to convert and compare it in database and pull up datetime.

  protected void User_Querytime()
    {
    DataClasses2DataContext dc1 = new DataClasses2DataContext();
    DateTime date1;


    string date = Request.QueryString.Get("TimeOfMessage");
    date1 = Convert.ToDateTime(date);

    var query7 = from u in dc1.syncback_logs
                 where u.TimeOfMessage = date1
                 orderby u.TimeOfMessage descending
                 select u;
    GridView1.DataSource = query7;
    GridView1.DataBind();
    }
Improve this question

6 Answers 6

Reset to default
3

I assume you're having a problem because date1 contains a date only, while your database contains full date/time values. To find matches you need to pick one of these approaches:

1) Remove the time information from the database values before comparing them to your target 2) Convert your target into a range, then find database values in that range.

List<DateTime> dateTimes = new List<DateTime>();
dateTimes.Add(DateTime.Parse("7/20/2008 7:14:53 AM"));
dateTimes.Add(DateTime.Parse("7/20/2008 12:12:01 AM"));
dateTimes.Add(DateTime.Parse("7/21/2008 9:00:00 AM"));
dateTimes.Add(DateTime.Parse("7/20/2009 7:14:53 AM"));

DateTime targetDate = Convert.ToDateTime("7/20/2008");

// Remove time info from data in database
var matchingDates = from date in dateTimes
                    where date.Date == targetDate
                    select date;

// Or use your target date to create a range
DateTime rangeStart = new DateTime(targetDate.Year, targetDate.Month, targetDate.Day, 0, 0, 0);
DateTime rangeEnd = new DateTime(targetDate.Year, targetDate.Month, targetDate.Day, 23, 59, 59);

var matchingDates2 = from date in dateTimes
                     where (date >= rangeStart) && (date <= rangeEnd)
                     select date;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Although I cannot test your exact problem, I was able to compare dates with the following code.

    // Random Date Collection
    List<DateTime> dateTimes = new List<DateTime>();
    dateTimes.Add(DateTime.Parse("7/20/2008 7:14:53 AM"));
    dateTimes.Add(DateTime.Parse("7/20/2008 7:14:54 AM"));
    dateTimes.Add(DateTime.Parse("7/20/2009 7:14:53 AM"));

    DateTime myDateTime = DateTime.Parse("7/20/2008");

    var query = from d in dateTimes
                where d.ToShortDateString() == myDateTime.ToShortDateString()
                select d;
Improve this answer

1 Comment

I don't think that'll work (if it does, it'll be slow), because you're converting to a string first (slow compare), and the ToShortDateString, I'm doubt that's translatable to SQL.
0

Instead of comparing the date directly, compare it two the min/max you'd accept. So instead of "DbDateCol = mydate" do "DbDateCol >= myDate.Date and DbDateCol < myDate.Date.AddDays(1)".

By using the Date property, you'll lop off the time component (force it to 0). By adding a day, you'll get the start of the next day, bounding it on the date of your DateTime instance.

Improve this answer

Comments

0

Assuming I'm understanding your question correctly, you should be able to just use

where u.TimeOfMessage.Date == date1
Improve this answer

Comments

0

Assuming that the TimeOfMessage attribute is of DateTime then you should be able to do TimeOfMessage.Date == date1

Improve this answer

Comments

0 
using System.Data.Objects;

use above and modify your query like below:

 var bla = (from log in context.Contacts
               where EntityFunctions.TruncateTime(log.ModifiedDate) < today.Date
               select log).FirstOrDefault();
Improve this answer

Comments