Q
Problem solve Get help with specific problems with your technologies, process and projects.

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


This was last published in April 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close