Creating a weekly report by specifying a query between two dates

I have a table with many records with dates. I want to create a weekly report by specifying a query between two dates. It was suggested that I try this:

     
 
  
 SELECT ORDER_DATE
    FROM Table1
    where date between '#form.start_date#' and '#form.end_date#'
     
 

But that doesn't work. Any suggestions?

You didn't say exactly what it is about it that "doesn't work" -- there are several things that could be wrong.

First of all, it is never a good idea to use a column name like "date" which is a reserved word in many databases -- this can result in syntax errors and it isn't always obvious what's causing them. Always call it ORDER_DATE or something similarly unique.

Hmmmm, that's curious -- you do have a column called ORDER_DATE! Perhaps your query should simply be

SELECT ORDER_DATE FROM Table1
 where ORDER_DATE between '#form.start_date#'
                      and '#form.end_date#'  

Another source of error could be that a value entered into one of your form fields is not in a valid date format, and the database is giving you a conversion error trying to convert it. Even if your form fields do contain valid dates, the database might be doing the comparison between character strings, not actual date fields.

Assuming you have some way of performing validation on the dates entered into your form fields, then the best way to do the query is with the Cold Fusion CreateODBCDate function, which will convert date strings into valid date objects.

SELECT ORDER_DATE FROM Table1
 where ORDER_DATE between #CreateODBCDate(form.start_date)#
                      and #CreateODBCDate(form.end_date)#

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.