[RESOLVED] DATE BETWEEN in SQL

[Kochanski]

I have a SELECT query for an Excel file that works if I do this:

da = New OleDb.OleDbDataAdapter("SELECT DATE, COMPOSER FROM [Sheet1$] WHERE DATE > 17/05/1903 ORDER by Date", con)

But not if I do this:

da = New OleDb.OleDbDataAdapter("SELECT DATE, COMPOSER FROM [Sheet1$] WHERE DATE BETWEEN '17/05/1903' AND '15/05/1926' ORDER by Date", con)

The error message just says there is a type mismatch but various alternatives don't seem to work. I'm trying to display data between two dates. Help appreciated.

[si_the_geek]

Thread moved to 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB.Net, it is certainly not specific to VB.Net)

The first query you showed will not give the correct result, as it contains a mathematical formula rather than a Date (and the result will be treated as smaller than all dates).

The second query is better, but has either one or two problems - the order of the date parts is wrong, and the delimiters are probably wrong too.

For an explanation and examples of how it should be, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum). I'm fairly sure you want the "Access" delimiters for dates.


However, both of those issues (and many more) can be avoided by using Parameters. For an explanation of why you should be using parameters (and links to code examples), see the FAQ article Why should I use Parameters instead of putting values into my SQL string?

[techgnome]

Not to mention that Date is potentially a reserved word and so shouldn't be used as a field name.
http://www.vbforums.com/showthread.php?t=449775

-tg

[Kochanski]

Thanks for the swift help. I've given myself a slap on two counts. In fact I have solved this with the syntax BETWEEN #05/17/1903# AND #05/24/1926# using the format #MM/DD/YYYY#