Getting month using the WHERE clause

Report · Aug 19, 2009

Hi everyone,

I have an annual calendar that I output on my Web site. I want to give my users the option to view the calendar month by month. The dates are input in my database in a field called "Date" in the m/d/yyyy format. When the user clicks the link to view the January, February, etc. calendar, I thought it would create a url variable (calendar.cfm?month=january) (or the number of the month, march=3, april=4 [calendar.cfm?month=3])

Here is my code:

SELECT *
FROM Calendar
WHERE Date LIKE '%#CalMonth#%'
ORDER BY Date ASC, StartTime ASC

</cfquery>

The above works, but it outputs every day and month that has to do with whatever month number/word I use. If CalMonth is 3 or March, I want just the dates for the month of March to show up.

I hope this makes sense. Thanks in advance to anyone who can help!

Report · Aug 19, 2009

You didn't say what type of database you have. For Microsoft SQL Server, you can use the SQL function DATEPART, as below. Microsoft Access has a Datepart function, I presume it works similary but the syntax might be different.

Couple of suggestions:

1) Avoid using words such as Date or Calendar for names of object like a database, a table, field, variable, etc.. It may work in this case, but you will run into problems if you don't follow a better scheme for naming things.

2) You may have code that validates the URL variables, but what is shown is totally vulnerable to SQL injection etc.. Aside from using cfqueryparam, which would protect you from SQL injection but is only part of a decent security model, one effective way is to only allow acceptable values. In this case, the only acceptable values for URL.month is a number from 1 to 12. You could use programming that ensures the provided URL value is an integer between 1 and 12. I favor using lists, which lets you use the same system for numbers, strings, etc. Obviously, it only works for relatively small lists, or lists that come from a query etc. But I find it extrermely effective and easy to use. In this case, you could use ListFind but I used ListFindNoCase which works for numbers or other characters.

</cfif>

SELECT    *
FROM       Calendar
WHERE    DATEPART(month, Date) = #ThisMonth#

Report · Aug 19, 2009

If your date field is a char datatype, change it now. Then, among other things, you can use the date functions in the other answer.

Report · Aug 19, 2009

If this is a really big db, I would suggest denormalizing the date into 3 columns if you are going to do a lot of querying of this type. Year int, day int, month int.

Reason being you can index these columns and query individually without having a sql in your where clause. Which doing so adds extreme overhead. Example:

select * from mytable where month(theDateCol) = 5

For each row SQL has to perform the function and cannot use any indexes to filter (more or less, I won't get into it further).

Of course this may not be feasabile, or it's a smaller database.

Either way, I would set 2 variables, one start date, one end date and do the query that way. Overall this is a better solution for where clauses on a date time.

declare @sdate datetime

declare @edate datetime

set @sdate = '1/1/2008'

set @edate = '1/31/2008'

select *

from myTable

where myDateCol between @sdate and @edate

Also if you want months across multiple years, I would definitely denormalize the columns. You could even do

Calendar.Date datetime

Calendar.theYear tinyint

Calendar.theMonth tinyint

Calendar.theDay tinyint

The amount of data stored is not that bad, and has the most flexibility for performance and growth.

The 4 columns above is something you might typically see in a data warehouse to perform filtering on huge datasets.

Byron Mann

mannb@hostmysite.com

byronosity@gmail .com

Lead Software Architect

hosting.com | hostmysite.com

http://www.hostmysite.com/ ?utm_source=bb

Report · Aug 19, 2009

A variation of this is to have a dates table. You can put the parts into separate fields and add other things like fiscal year, fiscal quarter, holiday, year-month, etc. Depends on your business needs.

Report · Aug 20, 2009

Thanks for everyone's interesting and thorough replies. The database I'm referring to is built in Access. It's not very big, and never will be very big. The Date field in Access is set up as a Date/Time field. I tried mkane1's suggestion, but I received a CF error that the date field was undefined:

WHERE DatePart("m",Date) = #ThisMonth#

Any suggestions?

Thanks again.

Report · Aug 20, 2009

Date functions vary from db to db. To find out which ones Access has, either google "msaccess date functions" or open access and press the F1 key.

Report · Aug 20, 2009

straffemp, I don't work with Access much. I found a reference at http://www.techonthenet.com/access/functions/date/datepart.php which gives the syntax in Access as DatePart ("m", [NameofDateField])

The CF error may be because the name of the field is "Date". I suggest making a copy of the table, change the field to something that definitely cannot be a keyword, i.e. Zdate, and see if the query works. If it does not, make sure that the query in the CF code is referring to correct table and field names, and that the variable is also correct.

BTW My first response addressed the question @ getting the month, which Datepart does, and a caution about naming conventions. There's always several ways to do things. For a big app, breaking down the field into parts might be advisable. I did not consider the year issue, which obviously must be taken into account in actual use. You could use Datepart in another criteria of the query, if you don't want to use multiple fields. As I said, I don't work with Access much, certainly not as a back end for web apps. For a high volume app, the "overhead" from using Datepart might well become a factor. I use SQL Server, and for regular type apps, the overhead would be negligible, even if there are thousands of rows.

Good luck!

Report · Aug 20, 2009

Thanks, mkane1. I tried your suggestions, but didn't have much luck. I feel like it's something simple that I'm missing. I changed the name of my table's date field to edate. Here's what I have now:

WHERE #DatePart("m", EDate)# = #ThisMonth#

When I run the page, CF tells me that EDate is undefined. If I put EDate in brackets (WHERE #DatePart("m", [EDate])# = #ThisMonth#), CF gives me a missing argument name error. This also happens if I put the DatePart statement in quotes (WHERE '#DatePart("m", [EDate])#' = #ThisMonth#). If I take the brackets out, CF tells me EDate is undefined.

Thanks again!

Report · Aug 20, 2009

straffemp, the Datepart function is an SQL function, not a CF variable. Your code should be

WHERE DatePart("m", EDate) = #ThisMonth#

Report · Aug 20, 2009

mkane1, when I use that line, this is the error I get:

[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2.

Everything looks like it should be working, but it's not! Thanks!

Report · Aug 20, 2009

Turns out that Access requires the parameter be enclosed in single quotes. At least, that's how it works on my MX 8.0.1 CF server connecting to an Access 2007 database. If I use double quotes, I get the "too few parameters" error. Change to single quotes, success!

WHERE DatePart('m', [z.ZDate]) = #ThisMonth#

Report · Aug 20, 2009

Still no luck for me. Getting "Too few paramenters. Expected 1" error. Out of curiosity, howcome you have [z.zdate] instead of just [zdate]?

Here is the whole thing so you can see it:

<cfquery name="getCalendar">
SELECT *
FROM CalendarTest
WHERE DatePart('m', [EDate]) = #ThisMonth#
ORDER BY Date ASC, StartTime ASC
</cfquery>

Report · Aug 20, 2009

straffemp, maybe it's a version thing. I created a simple Access 2007 database for testing, and do not get any error when using your query. I always use table aliases, e.g. tablename1 z, tablename2 y, etc. etc.. and then use z.fieldname, y.fieldname etc.. Doing so avoids conflicts and makes it easier to understand what's going on when looking at the code later. Since you only have one table, you don't need to do that, but it is a good practice.

Note this is an SQL thing, nothing to do with Cold Fusion. The aliases and prefixes ONLY appear inside a query.

For testing, hard-code ThisMonth. In fact, hard-code a number, like 3.

Also, your ORDER BY line still references a field named "Date". If you renamed that to EDate, this would cause an error. For testing,remove the ORDER BY line altogether. If this query doesn't work, well, you can take byron1021's advice to change your process to use full date values. Or have separate fields for the month etc.. That might (or might not, in this case) be a better approach, but I can't help but think that if you cannot get Datepart to work,you've got bigger problems....

<cfquery name="getCalendar">
SELECT *
FROM CalendarTest
WHERE DatePart('m', [EDate]) = 3
</cfquery>

Report · Aug 20, 2009

mkane1, can you believe it was throwing an error because my ORDER BY line was still date? Thanks for pointing that out. I knew it was something simple. That made everything work.

Thanks, too to Dan and byron1021 for their input. I am using the same table/query in multiple places on my site, so when time allows, I will implement some of your structural suggestions.

Report · Aug 20, 2009

Might be a good time to re-read byron1021's answer. Pay particular attention when you come accross the words, "Either way".