Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
Little OT: SQL Query Date Filter
Author: Tony Gruen
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126756
A huge thanks to all who took the time to respond. Now I am a little wiser
and it is working.
Tony Gruen
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126732
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/
Author: Scott Brady
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126731
>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/
Author: DURETTE, STEVEN J (AIT)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126730
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
Author: Tony Weeg
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126729
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
Author: Robertson-Ravo, Neil (RX)
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126728
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
Author: Tony Gruen
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:25207#126727
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
