• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Compare date in QoQ

Advisor ,
Apr 15, 2015 Apr 15, 2015

Copy link to clipboard

Copied

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,

Views

387

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 15, 2015 Apr 15, 2015

Copy link to clipboard

Copied

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).

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Apr 16, 2015 Apr 16, 2015

Copy link to clipboard

Copied

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Apr 16, 2015 Apr 16, 2015

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advisor ,
Apr 17, 2015 Apr 17, 2015

Copy link to clipboard

Copied

LATEST

It was close:

where ppbe_id_expiration='' OR ppbe_id_expiration>=<cfqueryparam value="#dateFormat(now(),'mm/dd/yyyy')#" cfsqltype="cf_sql_string" />

This is working and showing dates but not the records with empty/null values.

Thanks for your help.

Best,

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation