Skip to main content
J
jfb00
Inspiring
April 15, 2015
Question

Compare date in QoQ

  • April 15, 2015
  • 1 reply
  • 597 views

Hi all,

How can I compare a date in query of query? The date can be null in some records.

      <cfquery name="qryData" dbtype="query">

            select *

            from qryTempData

            where (datecompare(ppbe_id_expiration, dateFormat(now())) >= 0) OR (ppbe_id_expiration is NULL)

      </cfquery>

I also try:

       <cfquery name="qryData" dbtype="query">

            select *

            from qryTempData

            where (ppbe_id_expiration is NULL) OR (cast(ppbe_id_expiration as date) >= cast(dateFormat(now(),'mm/dd/yyyy') as date)

        </cfquery>

Using CF9.

Thanks,

    This topic has been closed for replies.

    1 reply

    S
    Steve Sommers
    Legend
    April 15, 2015

    Couple suggestions:

    1. In my experience QoQ does not store NULLs; instead they are stored as empty strings ('').
    2. Try cfqueryparam:  where (ppbe_id_expiration = <cfqueryparam value="" cfsqltype="cf_sql_varchar" />') OR (ppbe_id_expiration >= <cfqueryparam value="#now()#" cfsqltype="cf_sql_date" />)

    Lastly, your code does not show how the original query was created. If it was manually created using QueryNew(), the data columns need to be defined as date contents, otherwise values are treated as strings and you need to match whatever row format the strings are saved (and adjust the cfqueryparam parameters to match).

    J
    jfb00Author
    Inspiring
    April 16, 2015

    Thanks for your reply and help Steve.

    I am getting this error:

    Query Of Queries syntax error. Encountered "\'. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

    cause = [empty string]

    Any ideas?

    S
    Steve Sommers
    Legend
    April 16, 2015

    A syntax error in the where clause. If you are using the one I provided, try:

    where ppbe_id_expiration='' OR ppbe_id_expiration>=<cfqueryparam value="#now()#" cfsqltype="cf_sql_date" />

    Also, I vaguely remember having QoQ difficulties with date/time fields before. I think I solved it by making sure the date/time fields were in a string format that I controlled: yyyy-mm-dd HH:mm:ss. Then I made sure my comparison values used the same string format. I don't have samples as it's been a while but this might be an option for you. Don't include the time portion if you don't require it.

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