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,
Copy link to clipboard
Copied
Couple suggestions:
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).
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?
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.
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,