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?
Comments
-
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!
0 -
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'
endbut it doesn't
0 -
Have you tried without formatting the call_date?
CASE
WHEN 'call_date' = CURDATE() THEN 'Today'
WHEN 'call_date' = CURDATE()-1 THEN 'Yesterday'
END0 -
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.
-----------------
Chris1 -
Thanks, Chris. Unfortunately, call_date is a date/time field and will not compare to Curdate.
0 -
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 '='
0 -
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'
endyou might have to adjust for UTC time but I dont know what you time zone is or how your dates come to Domo
Domo Arigato!
**Say 'Thanks' by clicking the thumbs up in the post that helped you.
**Please mark the post that solves your problem as 'Accepted Solution'0 -
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
-----------------
Chris0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 299 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 55 App Studio
- 40 Variables
- 682 Automate
- 175 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 10 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive