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.
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.