none
linq to sql date comparison

    Question

  • In a C# 2010  application I am  using a linq to sql statement to compare a C# datetime field to a sql server 2008 r2 datetime field. By doing this comparison I am getting inconsistent results. Thus I am guessing a C# datetime field is not the same as a sql server 2008 r2 datetime value.

     Thus for  this comparison to work,  I would like to know how to change a C# datetime field to a sql server date time field. I would think I would need to do this conversion prior to comparing the dates.

    I want to compare to see if sql date > minimum date (1/1/1753) and sql date < current today's date. Can you tell me how to write this comparision?

    Thus now you show me code and/or point me to a reference that will show me how to solve this problem?

    Wednesday, February 27, 2013 12:57 PM

Answers

  • Hi,

    You should use .Date:

    from d in db.TestTable
    where d.TestDate > DateTime.MinValue.Date && d.TestDate < DateTime.Today.Date  
    select d.TestDate

    Or something like this:

    DateTime start = new DateTime("4/25/2011");
    DateTime end = new DateTime("5/2/2011");
    var result = db.TestTable.Where(d => d.TestDate  >= start 
                     && d.TestDate  <= end);

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Wednesday, February 27, 2013 1:41 PM
  • I just want to mention that there are two date types in SQL Date and DateTime. C# DateTime is always converted to SQL DateTime and if your DB field is type of date and you are trying to compare times this can really beak your logic.

    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Friday, March 01, 2013 3:01 PM

All replies

  • Hi,

    You should use .Date:

    from d in db.TestTable
    where d.TestDate > DateTime.MinValue.Date && d.TestDate < DateTime.Today.Date  
    select d.TestDate

    Or something like this:

    DateTime start = new DateTime("4/25/2011");
    DateTime end = new DateTime("5/2/2011");
    var result = db.TestTable.Where(d => d.TestDate  >= start 
                     && d.TestDate  <= end);

    Regards,

    JA Reyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solución de esta pregunta te ha sido útil.

    Wednesday, February 27, 2013 1:41 PM
  • I just want to mention that there are two date types in SQL Date and DateTime. C# DateTime is always converted to SQL DateTime and if your DB field is type of date and you are trying to compare times this can really beak your logic.

    Please Mark as Reply and Vote as Helpful if I helped.

    Also please visit my blog http://msguy.net/

    Friday, March 01, 2013 3:01 PM