Getting month using the WHERE clause

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.

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

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.

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.

<cfset ThisMonth = "0">

<cfset AllowedVals = "1,2,3,4,5,6,7,8,9,10,11,12">

<cfif ListFindNoCase(AllowedVals , URL.month)>

    <cfset ThisMonth = URL.month>

</cfif>

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