Little OT: SQL Query Date Filter

I have been testing this and cannot envision a solution. SQL Server 2000 and CF5. I have a small table that records transactions. The date for each transaction is recorded in createodbcdatetime format. Transactions can occur throughout the day. I am creating a page that shows the days transactions. The user enters a date in mm/dd/yyyy format to query the db. Trouble in that witht he query below when I enter 07/01/2003 I get transactions from 06/30/2003 as well. <CFSET CDate = "#createodbcdate(form.RptDate)#"> (this is what the user enters as the desired date for the report) <CFSET EDate = DateAdd('d', -1, CDate)> (yesterday's date) <CFSET TDate = DateAdd('d', 1, CDate)> (tomorrows date) SELECT * FROM Transactions WHERE bDate > #variables.EDate# AND bDate < #variables.TDate# Thank you in advance for your brainpower - is my method off somehow? Tony Gruen

SELECT * FROM Transactions WHERE bDate between #variables.EDate# AND #variables.TDate# where the dates can be any two dates, the query will return allthings matching, including things *on* those dates. using < less than and > greater than, excludes stuff not there...does this make sense... so if I only wanted the trx's for today, I would say select * from transactions where date between '07/01/2003 00:00:00.000' and '07/01/2003 23:59:59.999' hth tw tony weeg uncertified advanced cold fusion developer tony at navtrak dot net www.navtrak.net office 410.548.2337 fax 410.860.2337 I have been testing this and cannot envision a solution. SQL Server 2000 and CF5. I have a small table that records transactions. The date for each transaction is recorded in createodbcdatetime format. Transactions can occur throughout the day. I am creating a page that shows the days transactions. The user enters a date in mm/dd/yyyy format to query the db. Trouble in that witht he query below when I enter 07/01/2003 I get transactions from 06/30/2003 as well. <CFSET CDate = "#createodbcdate(form.RptDate)#"> (this is what the user enters as the desired date for the report) <CFSET EDate = DateAdd('d', -1, CDate)> (yesterday's date) <CFSET TDate = DateAdd('d', 1, CDate)> (tomorrows date) SELECT * FROM Transactions WHERE bDate > #variables.EDate# AND bDate < #variables.TDate# Thank you in advance for your brainpower - is my method off somehow? Tony Gruen

erm....how about : WHERE bDate >= #variables.EDate# 00:00:00 AND bDate <= #variables.TDate# 23:59:59 SQL Server uses the timestamp alongside the date as well. I have been testing this and cannot envision a solution. SQL Server 2000 and CF5. I have a small table that records transactions. The date for each transaction is recorded in createodbcdatetime format. Transactions can occur throughout the day. I am creating a page that shows the days transactions. The user enters a date in mm/dd/yyyy format to query the db. Trouble in that witht he query below when I enter 07/01/2003 I get transactions from 06/30/2003 as well. <CFSET CDate = "#createodbcdate(form.RptDate)#"> (this is what the user enters as the desired date for the report) <CFSET EDate = DateAdd('d', -1, CDate)> (yesterday's date) <CFSET TDate = DateAdd('d', 1, CDate)> (tomorrows date) SELECT * FROM Transactions WHERE bDate > #variables.EDate# AND bDate < #variables.TDate# Thank you in advance for your brainpower - is my method off somehow? Tony Gruen

Sounds like you are using SQL Server. This is expected behavior because 6/30/2003 01:01:01.01 is greater than 6/3/2003 00:00:00.03 (think that's right).  The second one is what you are creating. I believe that what you really want is : Select * from transactions where bDate >= #variables.EDate# and bDate < #Variables.CDate# but set EDate = today not yesterday. Steve I have been testing this and cannot envision a solution. SQL Server 2000 and CF5. I have a small table that records transactions. The date for each transaction is recorded in createodbcdatetime format. Transactions can occur throughout the day. I am creating a page that shows the days transactions. The user enters a date in mm/dd/yyyy format to query the db. Trouble in that witht he query below when I enter 07/01/2003 I get transactions from 06/30/2003 as well. <CFSET CDate = "#createodbcdate(form.RptDate)#"> (this is what the user enters as the desired date for the report) <CFSET EDate = DateAdd('d', -1, CDate)> (yesterday's date) <CFSET TDate = DateAdd('d', 1, CDate)> (tomorrows date) SELECT * FROM Transactions WHERE bDate > #variables.EDate# AND bDate < #variables.TDate# Thank you in advance for your brainpower - is my method off somehow? Tony Gruen

>WHERE bDate >= #variables.EDate# 00:00:00 >AND bDate <= #variables.TDate# 23:59:59 Since that would get 3 days' worth (that would get you transactions on EDate and TDate, wouldn't this work: WHERE bDate >= #CDate# 00:00:00 AND bDate <= #CDate# 23:59:59 -------------------------------- Scott Brady http://www.scottbrady.net/

ah yeah, you got the picture ;-)  The key is that you need to include the timestamp info. >WHERE bDate >= #variables.EDate# 00:00:00 >AND bDate <= #variables.TDate# 23:59:59 Since that would get 3 days' worth (that would get you transactions on EDate and TDate, wouldn't this work: WHERE bDate >= #CDate# 00:00:00 AND bDate <= #CDate# 23:59:59 -------------------------------- Scott Brady http://www.scottbrady.net/             

A huge thanks to all who took the time to respond. Now I am a little wiser and it is working. Tony Gruen