Query example:
<cfquery name="jkgh" dbtype="hql">
SELECT COUNT(production_status_id)
FROM production s
where convert(varchar(10),s.production_end,111) =
'#dateformat(now(),"yyyy/mm/dd")#'
</cfquery>
I am trying to convert the 'production_end' timestamp to a date format (any format will do without the time) in order to compare it with today's date.
I have tried all the conversion methods I could find but all so far have given an error.
If this is not possible, then I will have to change the database structure to include two separate fields with date and timestamp... or abandon Hibernate and write an SQL query...
What am I missing?
CodePudding user response：
where convert(varchar(10),s.production_end,111) = '#dateformat(now(),"yyyy/mm/dd")#'
You shouldn't need to modify the date column. (Also, that type of comparison is generally discouraged because it's not sargable). The standard approach for returning rows with today's date is using:
WHERE production_end >= {TodaysDateAtMidnight}
AND production_end < {TomorrowsDateAtMidnight}
... or in other words
WHERE production_end >= '2022-04-15' -- Today is April 15, 2022
AND production_end < '2022-04-16'
If you're tempted to use BETWEEN, see: