| Posted By |
Discussion Topic: Sorting query output by current month
|
|
chrisdonkor |
07-26-2010 @ 2:56 PM |
|
|
New Member
Posts: 24
Joined: Apr 2008
|
I want to sort outputted data by current month. Below is the code I have written:
<cfquery name="AllNews" datasource="#dbase#" dbtype="ODBC">
SELECT *
FROM tblNews, tblNewsCategories, tblNewsCats
WHERE tblNews.tblNewsCatID = tblNewsCategories.tblNewsCatID
AND tblNewsCategories.prodcatID = tblNewsCats.prodcatID
AND tblNewsCategories.prodcatID = 16
AND tblNews.tblNewsDate = #DateFormat(Now(), 'mm')#
ORDER BY tblNewsID Desc
</cfquery>
It is given me error. Can somebody help me out?
Stay Blessed
Christian Adusu-Donkor
Managing Consultant
(DOT House: websites & beyond)
|
|
Webmaster |
|
07-26-2010 @ 3:08 PM |
|
|
Administrator
Posts: 4421
Joined: Jan 2002
|
what is the error? what type of database are you using?
Pablo Varando 
Senior Application Architect
EasyCFM.COM, LLC.
904.483.1457 \\ mobile
webmaster@easycfm.com \\email
\m/ (>.<) \m/
--- rock on ---
|
|
chrisdonkor |
|
07-26-2010 @ 3:21 PM |
|
|
New Member
Posts: 24
Joined: Apr 2008
|
Am using an MS Access database. The 'tblNewsDate' field is a Date/Time field with default value 'dd-mmm-yy'. I am not getting any error at all to indicate that there are not items in my database that has the current month as it's date which is not true. Thanks
|
|
megan |
|
07-26-2010 @ 4:08 PM |
|
|
Moderator
Posts: 2369
Joined: Jan 2003
|
the dateformat function is for people not databases
try where month(tblNews.tblNewsDate) = #Month(now())#
but you might need to add the current year too
hth ~megan
-------------------------------------------------------
"The chief cause of failure & unhappiness is trading what you want most for what you want now." ~Zig Ziglar
|
|
Ron |
|
08-14-2010 @ 1:45 AM |
|
|
Senior Member
Posts: 244
Joined: Oct 2004
|
or
tblNews.tblNewsDate <= <cfqueryparam value="#createDate(year(now()), month(now()), daysinmonth(now()))#" cfsqltype="cf_sql_timestamp">
AND tblNews.tblNewsDate >= <cfqueryparam value="#createDate(year(now()), month(now()), '1'" cfsqltype="cf_sql_timestamp"> order by tblNews.tblNewsDate
This message was edited by Ron on 8-14-10 @ 1:45 AM
|
|
cfSearchin |
|
08-16-2010 @ 3:07 PM |
|
|
Senior Member
Posts: 529
Joined: Feb 2008
|
quote:
tblNews.tblNewsDate <=
Note, that assumes "tblNewsDate" contains a date _only_. If the column contains both date and time (ie Aug 16, 2010 1:45PM), that comparison may end up excluding most records for the last day of the month. So it is safer to use a comparison like the one below. As it works in both cases. Obviously, use cfqueryparam too..
<!--- not tested --->
<cfset startDate = createDate(year(now(), month(now()), 1)>
<cfset endDate = dateAdd("m", 1, startDate)>
WHERE tblNewsDate >= #startDate#
AND tblNewsDate < #endDate#
In other words ..
WHERE tblNewsDate >= '2010-08-01'
AND tblNewsDate < '2010-09-01'
This message was edited by cfSearchin on 8-16-10 @ 3:07 PM
|
View The 3 New EasyCFM.COM ColdFusion Tutorials Posted 
