Skip to main content
I
Inkfast
Inspiring
April 25, 2007
Answered

Querying dates by quarter

  • April 25, 2007
  • 2 replies
  • 583 views
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.


    This topic has been closed for replies.
    Correct answer paross1
    Remove the single quotes. DatePart("q",LdSaledate) is a function call, and #Quarter# is numeric.

    Phil

    2 replies

    I
    insuractive
    Inspiring
    April 25, 2007
    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?
    I
    InkfastAuthor
    Inspiring
    April 25, 2007
    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?
    P
    paross1
    Participating Frequently
    April 25, 2007
    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
    I
    InkfastAuthor
    Inspiring
    April 25, 2007
    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.
    P
    paross1Correct answer
    Participating Frequently
    April 25, 2007
    Remove the single quotes. DatePart("q",LdSaledate) is a function call, and #Quarter# is numeric.

    Phil
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices