search between two dates

[waner michaud]

Hi, I have form that has a date_from and date_to.. One table (id, reg_no, reg_date, desc) I want to search between those two dates from the form against reg_date on the table.. Using vb.net and sql express.. Thanks in advance

[Thomas Krojer]

this can be done in sql with the 'between' construct in the 'where' clause.

[Dr Walt Fair PE]

What he said. If you want more flexibility you can also use something like "SELECT * FROM table WHERE (reg_date > " & MyDate1 & ") and (reg_date < " & MyDate2 & ")"

CQ de W5ALT

Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software

[waner michaud]

Thanks, I tried this: Select * from FireArmsTD_View Where (FrireArms_date > " & DateFrom & ") And (FrireArms_date < " & DateFrom & ") I got invalid column name for both ' & DateFrom & ' and ' & DateTo & ' thanks

[Dr Walt Fair PE]

As written, your "s are not correct and DateFrom is shown twice, but I'll assume those are just typo's. Make sure that DateFrom and DateTo are showing up as strings. I guess I forgot to mention that, and that they need to be enclosed in quotes. I usually create a string variable to check while debugging.

CQ de W5ALT

Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software

[Luc Pattyn]

Two warnings: 1. IIRC the SQL BETWEEN keyword is a bit dangerous as different implementations use different definitions; the issue is inclusive versus exclusive, i.e. does an exact match with a border get included or not. Read the doc, or use explicit comparisons. 2. Use SQLParameter to avoid all datetime formatting and regional settings issues; it's much better/cleaner/safer than string concatenation anyway. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

modified on Thursday, November 4, 2010 12:58 PM

[waner michaud]

Hi, I am a novice, just don't know how. will you guide me? thanks

[Luc Pattyn]

The documentation and examples on SQLParameter are plenty, maybe you want to read Beginners guide to accessing SQL Server through C#[^] which uses C# but the same types and ideas are valid in VB.NET :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum

Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.

[Scubapro]

Try this:

select * from FireArmsTD_View
where FrireArms_date between to_date('&DateFrom','mm-dd-yyyy') and to_date('&DateTo','mm-dd-yyyy')

Btw, is better to use the 'Database Board' for these kind of questions.