Dates in Query

[osbel]

I have a query and in it I need to find where there was activity during a specific month. The activity date is a date type in an access database. how do i put the query to that i can say where activity_date is in month__? like if I want activity in april then where activity_date is in 4. How do I do this?

Here is my code, the blank is where i need to put the month.

SELECT auditor.auditor_name, sum(audTime.Administrative) as Administrative1, sum(audTime.Preliminary_Survey + audTime.Report_Writing + audTime.Field_Work) as sumofdirect1
FROM AudTime, auditor
WHERE
auditor.auditor_code = audTime.auditor_code AND
auditor.Auditor_Code <> 'ANO' AND
auditor.Student=Yes AND
activity_date between #createODBCdate(start)# and #createODBCdate(end)# and
activity_date is ___________________
<cfif isDefined(&quot;audit&quot and not (audit eq '' or audit eq 'all')>
and audtime.audit_number = '#audit#'
</cfif>
GROUP BY Auditor_name

Thanks for the help.

 

[osbel]

Just as a note, I tried doing

Month(activity_date) = 2

and I got this error:

QUERY

The QUERY attribute of the tag does not specify the name of an available query

The error occurred while processing an element with a general identifier of (CFOUTPUT), occupying document position (50:1) to (50:31) in the template file c:\inetpub\

http://wwwroot\ams\Time_by_Audit_number_preview.cfm.

Date/Time: 11/01/01 14:29:52
Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0)
Remote Address: 127.0.0.1
HTTP Referer:

http://127.0.0.1/ams/Time_by_Audit_Number.cfm

Query String: audit_number=A-PMS-GEN-99-1&start=1/1/2000&end=9/30/2001

and it works when I don't have that line in.

 

[Glowball]

In Access I went to the Help files and looked up &quot;Date fields&quot; and found &quot;extracting part of date in query.&quot; You can do something like this:

SELECT auditor.auditor_name, sum(audTime.Administrative) as Administrative1, sum(audTime.Preliminary_Survey + audTime.Report_Writing + audTime.Field_Work) as sumofdirect1  
    FROM AudTime, auditor
    WHERE
        auditor.auditor_code = audTime.auditor_code AND
        auditor.Auditor_Code <> 'ANO'  AND
        auditor.Student=Yes AND
        activity_date between #createODBCdate(start)# and #createODBCdate(end)# and
        DatePart(&quot;m&quot;,[whichtableisthis.activity_date]) = 2 
        <cfif isDefined(&quot;audit&quot;) and not (audit eq '' or audit eq 'all')>
            and audtime.audit_number = '#audit#'
        </cfif>
    GROUP BY Auditor_name

Hope that helps!

 

[osbel]

Thanks, I also found what works is month(activity_date) = 2.
Its a SQL command so it should work for any query no matter the datasource. Also useful are Year() and Day().

 

[Glowball]

Those are actually functions and specific to the database (though many databases support things like Year()). Only aggregate functions like COUNT() and MAX() are ANSI SQL and accepted by all SQL-supporting database packages. Oracle especially will have its own set of functions that won't be found anywhere else.