Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Question Unanswered: No results returned by SELECT against datetime field

    I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.

    I was using in Access:
    Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
    I have changed this for SQL Server to:
    Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
    But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.

    Is there a particular format for the date I am comparing with?
    Am I missing something else in my query?

    The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.

    Many thanks for any help or comments you can provide.

  2. #2
    Join Date
    Nov 2003
    OK smalldatetimes are only accurate to 30 secs or so, so the actual date values used in the query (assuming US language parsing on your machine) are:

    2003-01-01 00:00:00 and 2004-01-02 00:00:00

    It's probably better to use either a cannonical date form (see books online) or an explicit language date, using less than without the equals:

    myDateField > '01 Jan 2003' AND
    myDateField < '02 Jan 2004'

    ...which will give the intended meaning of your SQL date range. That said, if there are dates between the values used, you should have got a result. Try checking for language parsing funnies in your text representation of the dates.

  3. #3
    Join Date
    Jan 2004


    I have found the problem. - I am a muppet!

    During the conversion between the Access DB and the SQL Server DB I managed to convert the field myDateField to VARCHAR(50) rather than DATETIME.

    D'Oh !!!

    That would be why the comparisons were not working.

    Oh well. Thank you for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts