Highlighted

Compare date in QoQ

Advisor ,
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

285

Likes

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

Compare date in QoQ

Advisor ,
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

286

Likes

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
Apr 15, 2015 0
Advocate ,
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).

Likes

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
Reply
Loading...
Apr 15, 2015 0
Advisor ,
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?

Likes

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
Reply
Loading...
Apr 16, 2015 0
Advocate ,
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.

Likes

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
Reply
Loading...
Apr 16, 2015 0
jfb00 LATEST
Advisor ,
Apr 17, 2015

Copy link to clipboard

Copied

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,

Likes

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
Reply
Loading...
Apr 17, 2015 0