My records get time stamped when they are entered. It is a simple
date/time field.
What is the correct syntax for retrieving ALL records entered LAST
MONTH.
So, if today (Now()) is ANY day in September, show me all the records
entered in August.
TIA,
Tim
-
From: Claremont, Timothy [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 21, 2005 1:01 PM
To: CF-Talk
Subject: All Items From LAST month
My records get time stamped when they are entered. It is a simple
date/time field.
What is the correct syntax for retrieving ALL records entered LAST
there's probably a pure SQL way to do it too, but since I'm better
with CF than SQL, my hybrid solution would be:
cfset lastMonth = month(dateAdd('m', -1, now())) /
cfquery name=myQuery datasource=myDSN
SELECT
columns
FROM
tablename
WHERE
endDate = dateAdd(s, -1, createDate(year(now()), month(now()), 1));
startDate = createDate(year(endDate), month(endDate), 1);
SELECT *
FROM table
WHERE timestamp BETWEEN #endDate# AND #startDate#
Don't forget your CFQUERYPARAMs
cheers,
barneyb
On 9/21/05, Claremont, Timothy [EMAIL PROTECTED]
And, of course, make sure you put the dates in the right order in the SQL. ;)
cheers,
barneyb
On 9/21/05, Barney Boisvert [EMAIL PROTECTED] wrote:
endDate = dateAdd(s, -1, createDate(year(now()), month(now()), 1));
startDate = createDate(year(endDate), month(endDate), 1);
SELECT *
FROM
PM
To: CF-Talk
Subject: Re: All Items From LAST month
there's probably a pure SQL way to do it too, but since I'm better
with CF than SQL, my hybrid solution would be:
cfset lastMonth = month(dateAdd('m', -1, now())) /
cfquery name=myQuery datasource=myDSN
SELECT
columns
My records get time stamped when they are entered. It is a
simple
date/time field.
What is the correct syntax for retrieving ALL records
entered LAST
MONTH.
So, if today (Now()) is ANY day in September, show me all
the records
entered in August.
cfset startdate =
developer
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--//-
-Original Message-
From: Charlie Griefer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 21, 2005 1:22 PM
To: CF-Talk
Subject: Re: All Items From LAST month
there's probably a pure SQL way
Charlie,
The problem I see off the bat is when I get to future years, it will retrieve
records from all years, as long as the MONTH matches.
No matter, though. I am on the right track and will modify your suggestion
accordingly.
Thanks!
there's probably a pure SQL way to do it too, but
Also, what happens when I try to run this method in January, and it attempts to
subract 1 from the month number?
Charlie,
The problem I see off the bat is when I get to future years, it will
retrieve records from all years, as long as the MONTH matches.
No matter, though. I am on the
The example uses dateAdd, which subtracts a month, not a month number.
so dateAdd(m, -1, '2005/1/1') yields 2004/12/1. If you did this:
createDate(year(now()), month(now()) - 1, day(now())), then you'd have
the problem you propose.
cheers,
barneyb
On 9/21/05, Tim Claremont [EMAIL PROTECTED]
yeah, i hadn't thought about the years thing :)
And Barney's most certainly right about the speed. setting 2 vars
(start date, end date) is quicker than running a function on every
record to get the month value.
but...in my defense, you wouldn't have any issues in January since my
method isn't
12 matches
Mail list logo
