sql datetime to compare with string

Question

if i have the following to access an sql database for a date to compare it to a string that a user enters:

public IQueryable<Audit> FindAllAuditsByNameDate(String name, String date)
        {
            return from audit in db.Audits
                   where audit.EventTime.ToString().Contains(date) && audit.User.UserName.Contains(name)
                   orderby audit.User.UserName
                   select audit;
        }

it fails is the user enters the "/" character in a date. how do i work around this?

Have you tried converting the date String to a DateTime type before comparing? — Dennis Rongo
– Dennis Rongo, Commented Apr 20, 2012 at 18:26
How about parsing the string to datetime and comparing then? — user610217
– user610217, Commented Apr 20, 2012 at 18:27
You can do something like this also -- EntityFunctions.TruncateTime(audit.EventTime) — Dennis Rongo
– Dennis Rongo, Commented Apr 20, 2012 at 18:29

Mark Byers · Accepted Answer · 2012-04-20 18:28:26Z

2

Try DateTime.Parse. It's able to understand a lot of the common formats for entering DateTimes.

DateTime dateStart = DateTime.Parse(date);
DateTime dateEnd = dateStart.AddDays(1);

return from audit in db.Audits
       where audit.EventTime >= dateStart &&
             audit.EventTime < dateEnd &&
             audit.User.UserName.Contains(name)
       orderby audit.User.UserName
       select audit;

If DateTime.Parse doesn't parse the format you want, you can always use DateTime.ParseExact and provide your own format strings.

answered Apr 20, 2012 at 18:28

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Habib · Accepted Answer · 2012-04-20 18:31:51Z

2

Considering EventTime is of type DateTime? you need to check it against its value. First convert the string date to DateTime

DateTime newDate = Convert.ToDateTime(date);
return from audit in db.Audits
                   where audit.EventTime.Value == newDate && audit.User.UserName.Contains(name)
                   orderby audit.User.UserName
                   select audit;

answered Apr 20, 2012 at 18:31

Habib

224k30 gold badges420 silver badges446 bronze badges

Comments

Pankaj · Accepted Answer · 2012-04-20 19:19:25Z

Warning - Don't use Contains.

Disadvantages of Contains

Suppose I have two list objects.

List 1      List 2
  1           12
  2            7
  3            8
  4           98
  5            9
  6           10
  7            6

Using Contains, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!

Answer to your original Question

public IQueryable<Audit> FindAllAuditsByNameDate(String name, String date)
{
    DateTime Dt;
    if (DateTime.TryParse(date, out Dt))
    {
        return from audit in db.Audits
                where audit.EventTime.ToString().Contains(date) && audit.User.UserName.Contains(name)
                orderby audit.User.UserName
                select audit;
    }
    return null;
}

TryParse

Returns a bool indicating whether it succeeded.

It just try/catch internally that why is implemented without exceptions so that it is fast.

Use it in case the value may be InValid.

Parse

Throws an exception.

Use it if you are sure the value will be valid

Collectives™ on Stack Overflow

sql datetime to compare with string

3 Answers 3

Comments

Comments

Disadvantages of Contains

Using Contains, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!

Answer to your original Question

TryParse

Parse

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Disadvantages of Contains

Using Contains, it will search for each List-1 item in List-2 that means iteration will happen 49 times !!!

Answer to your original Question

TryParse

Parse

Comments

Your Answer

Sign up or log in

Post as a guest

Related