Search for day that falls with in a month

[phamtum]

Form variables: (month, year)

DB tables: (book_05)
Columns: (id, b_date, details, admin_id, posted)

I want to search table book_05 with the form variable(s) of Month and or Year.

Example: If only the Month is passed (5 or May) I want to display all record
in the table book_05 with a b_date that falls in the Month of May 2005. Year
default to current year unless a specified year is passed.

I hope you can understand this? Thank you in advanced for your help!!!

[mxstu]

One method for extracting records for a specific month and year, is to use the
sql DATEPART() function. The exact syntax for DATEPART() depends on the
database you're using.

If I understand you correctly, when the user selects a year that has passed,
you want to reset the year (only) to the current year. What about future
years? Is that allowed?



<cfset form.monthNumber = 5>
<cfset form.yearNumber = 2004>
<cfset currentYearNumber = Year(Now())>

<!--- selected year has passed. reset to current year --->
<cfif form.YearNumber LT currentYearNumber>
<cfset form.yearNumber = currentYearNumber>
</cfif>

SELECT b_date
FROM book_05
WHERE DATEPART(m, b_date) = #form.monthNumber# AND
DATEPART(yyyy, b_date) = #form.yearNumber#

[phamtum]

Sorry, I forgot to mention that I'm using Access DB. Future year is not really
important because query would return "0". I tried the code above and got this
error, any suggestions?

Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 2.

The error occurred in C:\CFusionMX\wwwroot\temp9\kcnApp\bcalendar.cfm: line 36

34 : FROM book_05
35 : WHERE DATEPART(m, b_date) = #form.monthNumber# AND
36 : DATEPART(yyyy, b_date) = #form.yearNumber#
37 : </cfquery>
38 : </cfif>




--------------------------------------------------------------------------------

SQL SELECT b_date FROM book_05 WHERE DATEPART(m, b_date) = 5 AND
DATEPART(yyyy, b_date) = 2005
DATASOURCE EEadmin_kcn2
VENDORERRORCODE -3010
SQLSTATE 07002

[phamtum]

Sorry, I forgot to mention that I'm using Access DB. Future year is not really
important because query would return "0". I tried the code above and got this
error, any suggestions? Thanks...

Error Occurred While Processing Request
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Too few parameters. Expected 2.

The error occurred in C:\CFusionMX\wwwroot\temp9\kcnApp\bcalendar.cfm: line 36

34 : FROM book_05
35 : WHERE DATEPART(m, b_date) = #form.monthNumber# AND
36 : DATEPART(yyyy, b_date) = #form.yearNumber#
37 : </cfquery>
38 : </cfif>




--------------------------------------------------------------------------------

SQL SELECT b_date FROM book_05 WHERE DATEPART(m, b_date) = 5 AND
DATEPART(yyyy, b_date) = 2005
DATASOURCE EEadmin_kcn2
VENDORERRORCODE -3010
SQLSTATE 07002

[phamtum]

Never mind it was just missing the "'"..

SELECT b_date
FROM book_05
WHERE DATEPART('m', b_date) = #form.month#
AND DATEPART('yyyy', b_date) = #form.year#

It works great!
Thank you for your expertise!!!

[phamtum]

I have a quick for you, I have modified your code a bit and have add a couple
of conditional statements.

SELECT b_date, posted
FROM book_05
WHERE DATEPART('m', b_date) = #form.month#
AND DATEPART('yyyy', b_date) = #form.year#
<cfif dateType is "reportedDate" AND requestedDate neq "">AND b.b_date =
#CreateODBCDate(form.requestedDate )#</cfif>
<cfif dateType is "postedDate" AND requestedDate neq "">AND b.posted =
#CreateODBCDate(form.requestedDate )#</cfif>

my first <cfif> works great, but the second <cfif> is not returning any
records as it should.
In my Access DB both data type for POSTED and B_DATE are "date/time". The only
different is the Format in the design mode I have it set for "General Date"
for POSTED and B_DATE is blank.

Example of what the data looks like in the DB:
B_date: 6/7/2005
Posted: 6/7/2005 5:28:00 PM

I tried dateFormat on POSTED but got an error.

Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Undefined function 'dateformat' in expression.

<cfif dateType is "postedDate" AND requestedDate neq "">AND
dateFormat(b.posted, ?mm/dd/yyyy?) = #CreateODBCDate(form.requestedDate
)#</cfif>

Your advises are greatly appreciated, thank you...

[phamtum]

FYI:
I just tried removing the time in POSTED column and it works, but the time is important to me how can I keep the time and return this record?

[OldCFer]

Why don't you just use:
<cfset StartDAte= CreateDate(TheYear,TheMonth,1)>
<cfset EndDAte= DateAdd("m",1,StartDAte)>

SELECT b_date, posted
FROM book_05
WHERE b_date >= #StartDate#
AND b_date < #EndDate#

The problem with using the DB Datepart() functions is that those functions
have to be run on each row to see if it matches what you passed. It's much
more efficient if the DB can compare the it's dates direcly with the values you
pass.

[mxstu]

I think your current query returns (0) records because it is not requesting
the right information. When you use the "=" operator, on a date/time field, the
database evaluates both the date and time of the column values.

CreateODBCDate() creates a date with a "time" of midnight, so if the
form.requestedDate is "06/07/2005" then your query

AND b.posted = #CreateODBCDate(form.requestedDate)#

essentially translates to:

where the "posted" date and time = 06/07/2005 Midnight

I think what you really want is to retrieve all records with a "date" of
6/7/2005 and a time anywhere between midnight and 11:59:59 PM.






<!-- assumes that users can search by "reportedDate" or "postedDate", but not
both. --->
<!--- incorporates OldCfer's suggestion ---->
<cfset StartDate = CreateDate(form.yearNumber, form.monthNumber, 1)>
<cfset EndDate = DateAdd("m", 1, StartDate)>

<cfquery name="getData" datasource="myAccessDSN">
SELECT b_date, posted
FROM book_05 b
WHERE b_date >= #CreateODBCDate(StartDate)#
AND b_date < #CreateODBCDate(EndDate)#
<cfif IsDate(form.requestedDate)>
<cfif form.dateType is "reportedDate">
AND b.b_date >= #CreateODBCDate(form.requestedDate)#
AND b.b_date < #CreateODBCDate(DateAdd("d", 1, form.requestedDate))#
<cfelseif form.dateType is "postedDate">
AND b.posted >= #CreateODBCDate(form.requestedDate)#
AND b.posted < #CreateODBCDate(DateAdd("d", 1, form.requestedDate))#
</cfif>
</cfif>
</cfquery>