none
SQL: select * from tb Where time = '11/02/2010' throws a error... RRS feed

  • Question

  • vb express 2008

    DB: SQLce

    Problem: I can't select rows based on time-values:

    sql-code:

    SELECT     time, Waarde
    FROM         Data
    WHERE     (time = '22/04/2010')

     

    This is the error message I get:

    SQL Execution Error:

    Executed SQL statement: Select time, waarde from data where (time='22/04/2010')
    error source: sql server compact ado.net data provider
    Error message: the data type isnot valid for the boolean operation.[data type(if known) =datatime, data type(if known) = nvarchar]

     

    anyone a solution? tnx

    Sunday, April 25, 2010 4:01 PM

Answers

  • Since you haven't gotten a reply on this...

    I'm not familiar with Compact Edition, so just guesses:

    1.  Try mm/dd/yyyy format instead for the literal.

    2.  Instead of using the literal, put a @parameter in its place and use parameterized SQL to pass a .NET DateTime value.  This will ensure there is no conversion from nvarchar occurring and makes it unambiguous what should happen in different locales. This is actually a good practice, for security (SQL injection) and other reasons.

     

    Tuesday, April 27, 2010 2:27 AM
  • See http://erikej.blogspot.com/2009/06/scripting-sql-datetime-fields-and.html
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, April 27, 2010 12:47 PM
  • Hi lifetec,

    What's the data type of time column in table Data ? From the error message I see "data type(if known) =datatime",  is it datetime ?
    The error message seems to say the data type is not valid, please have a check.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 28, 2010 9:39 AM
    Moderator

All replies

  • Since you haven't gotten a reply on this...

    I'm not familiar with Compact Edition, so just guesses:

    1.  Try mm/dd/yyyy format instead for the literal.

    2.  Instead of using the literal, put a @parameter in its place and use parameterized SQL to pass a .NET DateTime value.  This will ensure there is no conversion from nvarchar occurring and makes it unambiguous what should happen in different locales. This is actually a good practice, for security (SQL injection) and other reasons.

     

    Tuesday, April 27, 2010 2:27 AM
  • string nowdate = DateTime.Today.Date.ToShortDateString(); 
    
    SELECT time, Waarde FROM Data WHERE time = '" + nowdate + "';

    If (My Answer) Please mark the replies as answer. Thanks; Else Thank you all the same;
    Tuesday, April 27, 2010 6:41 AM
  • See http://erikej.blogspot.com/2009/06/scripting-sql-datetime-fields-and.html
    http://erikej.blogspot.com Erik Ejlskov Jensen - Please mark as answer, if this was it.
    Tuesday, April 27, 2010 12:47 PM
  • Hi lifetec,

    What's the data type of time column in table Data ? From the error message I see "data type(if known) =datatime",  is it datetime ?
    The error message seems to say the data type is not valid, please have a check.

    Best regards,
    Alex Liang


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Wednesday, April 28, 2010 9:39 AM
    Moderator