Database Query Only Show Current Month Articles

[GoBigOrange]

Right now we are having to go in and manually change the date to only show
this month and years articles, but we'd like to change the query so we can
only show this month, year using a variable. Here is the query can someone
tell me what we would need to change it to only show the current months
articles without manually doing it?


WHERE type='news' and date > '07/01/2005' and date < '7/31/2005'


Thank in advance.

[jdeline]

I'm sorry, but 07/01/2005 just isn't going to fly. What is the format of the
date field in your database? The current month's articles for this year are
DateFormat(Now(), "yyyymm"), which is what you could be storing in the
database. That gives you 200508 for this month.

[GoBigOrange]

We are already doing a query on the database using that code WHERE type='news'
and date > '07/01/2005' and date < '7/31/2005'

I looked at the database in enterprise manager and the data format is 4/2/2005
9:22:29 AM

The problem is, we don't need to manually put the dates in, what would the
code be to pull out the last 30 days

[mxstu]

Originally posted by: GoBigOrange
We are already doing a query on the database using that code WHERE type='news'
and date > '07/01/2005' and date < '7/31/2005'


I think the point was that the query above only gives you records "dated"
between 7/2/2005 and 7/30/2005, which is not the entire month of July.




<!--- find all records "dated" within the current month --->
<!--- example: 08/01/2005 and 08/31/2005 --->
<cfset firstOfMonth = CreateDate(Year(Now()), Month(Now()), 1)>
<cfquery name="getNews" datasource="myAccessDSN">
SELECT Type, DateColumn
FROM YourTable
WHERE type='news' AND
dateColumn >= #CreateODBCDate(firstOfMonth)# AND
dateColumn < #CreateODBCDate(DateAdd("m", 1, firstOfMonth))#
</cfquery>

<!--- find all records "dated" in the last 30 days --->
<!--- example: if today is 08/20/2005 .... --->
<!--- returns records "dated" between 07/22/2005 through 08/20/2005 .... --->
<cfquery name="getNews" datasource="myAccessDSN">
SELECT Type, DateColumn
FROM YourTable
WHERE type='news' AND
dateColumn >= #CreateODBCDate(DateAdd("d", -29, Now()))# AND
dateColumn < #CreateODBCDate(DateAdd("d", 1, Now()))#
</cfquery>

[GoBigOrange]

Thanks for the code, it worked like a charm!

[GoBigOrange]

Okay one more question and I will leave you guys alone! We have a thing that
shows the next home game on the site. What I would like to do is show a summary
of the next 5 home games or perhaps all the home games for the current month.
Here is the query that shows the next home game:

<cfquery name="gameToday" datasource="#mainDSN#"
username="#mainDSNUsername#" password="#mainDSNPassword#">
SELECT *
FROM schedule
WHERE date='#dateformat(now(),'mm/dd/yyyy')#' AND (Score IS NULL or Score =
'') and home = 1 and final != 1
</cfquery>

[mxstu]

The next five future games from a specific date.. or the current date? If from
the current date, should it include a game scheduled for the current date?

To show all the games for the current month, you can just adapt the first
query in my example above. It begins with the comment line : <!--- find all
records "dated" within the current month --->

[GoBigOrange]

My date format is different on this table, perhaps that is the problem the
dates are formatted like 10/21/2005. I keep getting a processing error. One of
the things is the home has to be 1 so I have a where statement too.




<cfset firstOfMonth = CreateDate(Year(Now()), Month(Now()), 1)>
<cfquery name="games" datasource="#mainDSN#" username="#mainDSNUsername#"
password="#mainDSNPassword#">
SELECT *
FROM schedule
WHERE home = 1
date >= #CreateODBCDate(DateAdd("d", -29, Now()))# AND
date < #CreateODBCDate(DateAdd("d", 1, Now()))#
</cfquery>

[mxstu]

> My date format is different on this table, perhaps that is the problem

> the dates are formatted like 10/21/2005.

What database are you using? Is the column a date/time column? What do you
mean by the format is different than your other table?

[GoBigOrange]

the column is called date and when we put the schedules in we enter the date of
the game like 10/21/2005. Probably should have been called something else, but
someone else did it! 10/21/2005 is a sample of a date in the column.

[mxstu]

You didn't say whether or not it is a date/time column.

Also, "Date" is a reserved word, so using it as a column name will likely cause problems.

[mxstu]

>Also, "Date" is a reserved word, so using it as a column name will likely
cause problems.

I take that back. You can use "Date" as a column name in SQL Server database,
but using another name would be better.

[GoBigOrange]

that's what I was afraid of. time to rename column.

[mxstu]

Did you see my question?

You didn't say whether or not it is a date/time column.

[GoBigOrange]

no, somebody has named the column date. And they should have called it gamedate or something. The newletter using a real data field that timestamps.

[mxstu]

I'm talking about the column data type. For example, sql server uses the
"date/time" data type to store dates, "varchar" to and "char" to text
characters, etc... Anyway, I think your problem may be that you're missing an
AND in your statement:

SELECT *
FROM schedule
WHERE home = 1 missing an AND here ....
date >= #CreateODBCDate(firstOfMonth)# AND
date < #CreateODBCDate(DateAdd("m", 1, firstOfMonth))#

[mxstu]

Originally posted by: GoBigOrange
no, somebody has named the column date. And they should have called it
gamedate or something. The newletter using a real data field that timestamps.

The response was not quite clear ;-) If "Schedule.Date" is NOT a "date/time"
column.... then you are correct... using #CreateODBCDate()# will not work
because it is intended for use with "date/time" columns. If this is the case,
you should consider changing the column's data type to "date/time". Dates
should be stored as dates and not text. Just be aware that the change may
affect other queries that insert into or update this field.

[GoBigOrange]

The column data time is datetime ....sorry. :-)

[mxstu]

There should not be any problem if the data type IS "date/time". Did you
correct the syntax error I mentioned in the original query?

SELECT *
FROM schedule
WHERE home = 1 AND <--- missing an AND here
date >= #CreateODBCDate(firstOfMonth)# AND
date < #CreateODBCDate(DateAdd("m", 1, firstOfMonth))#

[GoBigOrange]

Yeah, the and sort of helps, huh? That did the trick. Thanks!