how do you format date for comparison to today

I have tried several iterations to compare the date field to CURDATE, CURRENTDATE, NOW and when I attempt to determine if they are equal, I get an error. Does anyone know the proper MYSQL to format for this comparison?

case

when DATE_FORMAT(`call_date`,'%m-%d-%Y')=CURDATE() then 'Today'

when DATE_FORMAT(`call_date`,'%m-%d-%Y')= CURDATE()-1 then 'Yesterday'

end

Thank you in advance

Find more posts tagged with

Comments

Cartergan

I could be mistaken here but I beleive the CURDATE() format is in YYMMDD so it may be erroring out due to the formats not matching. Let me know if this fixes anything!

user11351

so then you think this would work

case
when date_format(`call_date`,%y %m %d)=CURDATE() then 'Today'
when date_format(`call_date`,%y %m %d)=CURDATE()-1 then'Yesterday'
end

but it doesn't

Cartergan

Have you tried without formatting the call_date?

CASE
WHEN 'call_date' = CURDATE() THEN 'Today'
WHEN 'call_date' = CURDATE()-1 THEN 'Yesterday'
END

cwolman

The problem is that the date_format function returns a string value not a date value. As long as `call_date` is a date field then you should be able to compare to the curdate(). If `call_date` is text then you will need to convert it to date before doing date comparisons.

user11351

Thanks, Chris. Unfortunately, call_date is a date/time field and will not compare to Curdate.

user11351

Thanks. Unfortunately, that gave me an error

The database reported a syntax error. Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,NUMERIC) for operation '='

Godiepi

Hi @user11351

if all you are trying to do is tag today's date as "Today" and Yesterday's date as "Yesterday" then do this

Case 
when Date(`call_date`) = Current_Date() then 'Today'
when Date(`call_date`) = Date_Add(Current_Date, interval -1 day) then 'Yesterday'
end

you might have to adjust for UTC time but I dont know what you time zone is or how your dates come to Domo

cwolman

I am not sure how the table in domo can have a different collation but that is what the error is stating. Personally I have never seen this error but you may be able to run your query by using the BINARY operator. Even if this resolves you issue I would open a ticket with support to determine the root cause of the issue.

case
when binary `call_date`= binary CURDATE() then 'Today'
when binary `call_date`=binary CURDATE()-1 then 'Yesterday'
end