locked
date compare RRS feed

  • Question

  • Hi,

     

    I need to compare date to get the values from SQL EXPRESS. My code is the following:

     

    conn.Open();

     

     

    strDate=”23/02/2007”;

    strSql=”Select fldDate from tbl where fldDate <= ‘ “  +  DateTime.Pares(strDate).ToString(“yyyy/MM/dd”)  + “ ’ ” ;

     

    cmd=new SalCommand( strSql , conn);

    reader=cmd.ExecuteReader();

    while (reader.Read() )

    {

    Date.Text= reader[“fldDate”].toString();

    }

    conn.Close();

     

     

     

    I get nothing while there are a lot of records in the table.

    Sunday, July 22, 2007 6:55 AM

Answers

  • Try

     

    Code Snippet

    ToString("yyyy-MM-dd")

     

    Sunday, July 22, 2007 7:03 AM

All replies

  • Try

     

    Code Snippet

    ToString("yyyy-MM-dd")

     

    Sunday, July 22, 2007 7:03 AM
  • thanks a lot

     

    It works I change the order of the date to  strDate=”2007/02/23” and it works.

    Sunday, July 22, 2007 7:08 AM
  • hi again,

     

    i tried to use Like to get all data in the selected date but unfortunately it did not work

     

    strDate=”23/02/2007”;

    strSql=”Select fldDate from tbl where fldDate Like ‘ % +  DateTime.Pares(strDate).ToString(“yyyy/MM/dd”)  + “ %’ ” ;

     

     

    can anybody help

    Sunday, July 22, 2007 9:58 AM
  • If you want to select all the record of that date, try using equal sign (=)

    Sunday, July 22, 2007 10:03 AM
  • I tried and it did not work

     

    strSql=”Select fldDate from tbl where fldDate = ‘23/02/2007 ” ;

     

    I think because it is trying to look for ‘23/02/2207 00:00:00’ 

     

    And I am looking for any data in that date regardless of the time

     

    Sunday, July 22, 2007 10:10 AM
  • I have write you about working with DateTime values when executing commands on Sql Server database:
    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1893848&SiteID=1
    What you should change in the command is to replace the command text in this one:
    "SELECT fldDate FROM tbl WHERE DateDiff(d, fldDate, @Date) = 0"

    And command instead of ExecuteNoneQuery would probably be ExecuteReader() and read the results.
    This command as it looks like is for testing purposes, or maybe a part of other command text, because that code is weird. If you like to see if there are records for particular day, then "Select Count(*) From" would be better.
    Sunday, July 22, 2007 4:28 PM
  • This is something I've hit numerous times. Boban's suggestion certainly works, and there are variations such as converting the date in the table to a varchar without the time and so on.

    Unfortunately they all involve doing something to the data for every row, which is a bit of a perfomance hit.

    Another option is to use something like "SELECT fldDate FROM tbl WHERE fldDate between @Date and DateAdd(d, 1, @Date)".

    The DateAdd is only performed once, which should make this method faster. As the between statement is inclusive you might want to
    add a where clause along the lines of "and fldDate != DateAdd(d, 1, @Date)", to avoid returning results for ‘23/02/2207 00:00:00’ when @Date = 22/2/2207.

    Note that I haven't done performance comparisons of the different methods, but from what I know the one I've suggested should be faster. And if you have an index on the date field then this method should allow the index to be used, whereas the other methods wouldn't. This alone could provide a very large performance benefit.

    Regards,

    Sean
    Monday, July 30, 2007 6:54 PM
  •  Sean Fowler wrote:
    Another option is to use something like "SELECT fldDate FROM tbl WHERE fldDate between @Date and DateAdd(d, 1, @Date)".

    The DateAdd is only performed once, which should make this method faster. As the between statement is inclusive you might want to
    add a where clause along the lines of "and fldDate != DateAdd(d, 1, @Date)", to avoid returning results for ‘23/02/2207 00:00:00’ when @Date = 22/2/2207.
    Sean


    Briliant idea Sean. I haven't tought in the first moment, and now i see the point. Having index on fldDate and using your code will be much faster.

    Tuesday, July 31, 2007 7:26 AM