CODEWITHSUNDEEP
c#sqlsql-server-2008datetime
Leron
Leron

Reputation: 9866

Convert string to a valid MS SQL datetime to use for search in database

I saw a lot of answers, most of which include using of DateTime.ParseExact and "CultureInfo.InvariantCulture` but it's not working for me and I'm not sure that those answers are 100% related to the problem I need to solve.

I have table with records from the database. The use can perform search based on different criteria one of which is date. I use some inherited jQuery calendar and in my controller the data from the date filed comes in format "dd/mm/yyyy hh:mm:ss". I want to be able to use this information to perform search in the database for records on the same date. The one problem is that I want to use only the date but not the time, but I'm not sure in what order to solve this problem so I decided to convert the incoming string to valid MS SQL datetime and see what happen and the to think about the time.

I tried different things, this is my last after which I decide to post here :

    if (!String.IsNullOrEmpty(selDate))
    {
        CultureInfo myCItrad = new CultureInfo("bg-BG", false);
        DateTime parsedDate = DateTime.ParseExact(
           selDate,
           "dd.MM.yyyy hh:mm:ss",
           myCItrad);
        model = model.Where(m => m.Date == parsedDate);
    }

Upvotes: 3

Views: 8392

Answers (4)

to StackOverflow
to StackOverflow

Reputation: 124706

The one problem is that I want to use only the date but not the time,

This has nothing to do with SQL date formats.

If you want to use only the date and not the time, you could do something like:

model = model.Where(m => m.Date.Date == parsedDate.Date);

or if you prefer:

model = model.Where(m => (m.Date >= parsedDate.Date && m.Date < parsedDate.Date.AddDays(1));

In the above m.Date.Date and parsedDate.Date you are using the DateTime.Date property to get the date component of your date value, discarding the time component.

Upvotes: 1

Dzi-100
Dzi-100

Reputation: 13

DateTime in SQL is this format yyyy-MM-dd HH:mm:ss. correct code is

DateTime parsedDate = DateTime.ParseExact(selDate, "yyyy-MM-dd" myCItrad);

Upvotes: 0

Amit Shkolnik
Amit Shkolnik

Reputation: 152

Leron,

Date issues between application and SQL are well known.

  1. SQL and .NET date time types are different: http://msdn.microsoft.com/en-us/library/system.data.sqltypes.sqldatetime.aspx
  2. On both SQL and .NET there are numerous date time formats, all need to be treated as numbers types.

So, you need to know how to convert numerous date time formats on .NET side, to convert it to SQL type, and vice versa. Not a good practice...

  1. Use SqlDateTime to conduct conversion. Or:
  2. A simple, a bit "dirty" solution, is something like that:
    • Take the date wanted by user. Calculate how many days is it from current (e.g. --> int days = (DateTime.Now-selectDate).Days;
    • Now on database query, do it using SQL: GetDate()-days

Upvotes: 1

Andrey Gordeev
Andrey Gordeev

Reputation: 32459

when I parse selDate the string doesn't contain them, it's - "23/05/2013 09:04:45"

If your input string looks like "23/05/2013 09:04:45" then you should use this pattern for ParseExact: "dd/MM/yyyy hh:mm:ss"

if (!String.IsNullOrEmpty(selDate))
{
    CultureInfo myCItrad = new CultureInfo("bg-BG", false);
    DateTime parsedDate = DateTime.ParseExact(
       selDate,
       "dd/MM/yyyy hh:mm:ss",
       myCItrad);
    model = model.Where(m => m.Date == parsedDate);
}

Upvotes: 4

Related Questions