none
Dataset.Tables("xxx").select("savdate = xxxxxxxx")

    Question

  • in the table exists the field "savdate" in format Date, there is date and time saved.

    trying to select one datarow from the datatable, there's nothing selected. string is "savdate = #5/12/2009 11:23:56#"
    Before selecting I formatted the date in the dateformat for english as MM/dd/yyyy hh:mm:ss PM/AM

    When I compare the date for selecting and all records in datatable I found the datarow with this date.

    When I used the following string "savdate > #5/12/2009 11:23:56#" for selecting, I got some datarows.


    Wednesday, December 30, 2009 1:39 PM

All replies

  • Hi wolfskamp,

    I have no idea why you save your date/time information as a string (why not use DateTime?). But if you really look for a string you must format the query this way:

    Dataset.Tables("xxx").Select("savdate='xxxxxxxxxx'"); // please note the quotes

    LE: You should also pay attention to the way you format the date string:
    string formattedDateString = DateTime.Now.ToString("MM/dd/yyyy hh:mm:ss tt", new System.Globalization.CultureInfo("en-US"));


    Marcel
    • Edited by Marcel RomaMVP Wednesday, December 30, 2009 2:51 PM Culture related date formatting
    Wednesday, December 30, 2009 1:56 PM
  • try Dataset.Tables("xxx").Select("CONVERT(VARCHAR(10),savdate,101)='*****'")

    In above code replace '*****' with the date in MM/dd/yyyy format.

    Eg: Dataset.Tables("xxx").Select("CONVERT(VARCHAR(10),savdate,101)='12/30/2009'")

    You can try other format also. For other format refer Date and Time styles in following link

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Gaurav Khanna
    Wednesday, December 30, 2009 2:32 PM
  • try Dataset.Tables("xxx").Select("CONVERT(VARCHAR(10),savdate,101)='*****'")

    CONVERT is a Transact-SQL function. It will not work with filtering client-side data.
    Wednesday, December 30, 2009 2:44 PM
  • I think you are correct. Was a mistake.
    Gaurav Khanna
    Wednesday, December 30, 2009 2:57 PM

  • There is a slight chance that I too got you wrong. Maybe you *do use* DateTime as the data type of your column, but loose information when converting to string. Try this code:

    static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        DataColumn dc = new DataColumn("savdate", typeof(System.DateTime));
        dt.Columns.Add(dc);
    
        DataRow row = dt.NewRow();
        DateTime date = DateTime.Now;
    
        row["savdate"] = date;
        dt.Rows.Add(row);
    
        string dateToSearchFor = date.ToString("MM/dd/yyyy hh:mm:ss.ffffff tt", new System.Globalization.CultureInfo("en-US"));
        DataRow[] rows = dt.Select("savdate=#" + dateToSearchFor + "#");
    
        Console.WriteLine(rows.Count());
        Console.ReadKey(true);
    }
    
    If you omit the milliseconds (ffffff) or have not enough precision (ff), you'll get no results.

    Marcel
    Wednesday, December 30, 2009 3:11 PM
  • Thanks for your answer, but I don't save the date as a string. The format in database is OleDbType.Date, so there's no need and no possiblity to set any option.

    The astonishing thing for me is this:
    When I compare the saved time in format

    dim savetime as date = FormatDateTime(currenttime, DateFormat.GeneralDate)

    dim comptime as date = FormatDateTime(searchtime, DateFormat.GeneralDate)
    if savetime = comptime then found  = true

    there is found the right record in my database. So I formated both "date" in same way to compare them.

    The question is "Why does the .select("formated time") fails ?

    Saturday, January 02, 2010 10:53 AM
  • I thinks this is because there is no record that conforms to the selection criteria you specify at runtime.

    Do the following: Take a DateTime object and save it to a Microsoft Access database. Then read it right back again from the database and compare the DateTime.Ticks of the saved value with the DateTime.Ticks of the original value. You'll see that the Access DateTime.Ticks fully misses the last 4 digits of the Ticks value. The two DateTimes are not identical.
    Saturday, January 02, 2010 6:05 PM
  • It works fine for me. Perhaps the actual date has also a millisecond part that you didn't see? Remember that the criteria will only look for the EXACT same date and time.
    • Marked as answer by wolfskamp Sunday, January 10, 2010 4:49 PM
    • Unmarked as answer by wolfskamp Sunday, January 10, 2010 4:50 PM
    Saturday, January 02, 2010 6:24 PM
  • With database files saved in the Access 2000 format, the last 4 digits of the DateTime.Ticks value are not saved.
    • Proposed as answer by Marcel RomaMVP Friday, October 08, 2010 10:01 AM
    Saturday, January 02, 2010 7:17 PM
  • Thanks, the ticks behind the INT of the seconds must be cut off before saving the date/time in the database.
    Sunday, January 10, 2010 4:52 PM