Querying dates by quarter

[Inkfast]

Greetings all!

After doing a bit of research I've written a query to return a list of results based on the Quarter. I got passed any errors but the query now returns zero results on all 4 Quarters. Not sure what I'm missing here. The field in the database is set to Date/Time. Any help would be greatly appreciated, David.

[paross1]

What database are you using. Since DatePart("q",LdSaledate) is enclosed within single quotes in the statement 'DatePart("q",LdSaledate)' = '#Quarter#', it is being treated as string not a database function call. The database type will determine what actual date/time functions are available to use.

Phil

[Inkfast]

The DB is Access. I took the code from an Access dev site and wrote it in that way so when CF wrote the SQL statement it would be readable by Access. I thought it would work that way.

[paross1 · Accepted solution]

Remove the single quotes. DatePart("q",LdSaledate) is a function call, and #Quarter# is numeric.

Phil

[insuractive]

EDIT - ah I see paross1 already posted this as I was typing it.

Have you tried paross1's suggestion?

<cfquery datasource="Leads" name="GetLeads">
Select *
From Ld_Tbl
Where LdAgent = '#LdAgent#'
And DatePart("q",LdSaledate) = #Quarter#
</cfquery>

Just to confirm - are you only storing data for 1 year or do you need to know what year the quarter occured in?

[Inkfast]

Phil's answer fixed it. The Leads will have been converted to either Sale or Dead by the time the same quarter would roll around in the next year so that won't be an issue. Just for the fun of it what if I did want to query Q/YYYY?

[insuractive]

You may also want to try using <cfqueryparam>'s in your query. Using #quarter# could open you up to SQL injection.

instead of

= #Quarter#

Try this:

= <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#Quarter#">

that should give you some protection and speed up your queries as well.

[Inkfast]

Good idea!. I have really got to take the time for some advanced classes. Thanks guys!!!