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?
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
when date_format(`call_date`,%y %m %d)=CURDATE() then 'Today'
when date_format(`call_date`,%y %m %d)=CURDATE()-1 then'Yesterday'
but it doesn't0
Have you tried without formatting the call_date?
WHEN 'call_date' = CURDATE() THEN 'Today'
WHEN 'call_date' = CURDATE()-1 THEN 'Yesterday'
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.
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
if all you are trying to do is tag today's date as "Today" and Yesterday's date as "Yesterday" then do this
when Date(`call_date`) = Current_Date() then 'Today'
when Date(`call_date`) = Date_Add(Current_Date, interval -1 day) then 'Yesterday'
you might have to adjust for UTC time but I dont know what you time zone is or how your dates come to DomoDomo 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.
when binary `call_date`= binary CURDATE() then 'Today'
when binary `call_date`=binary CURDATE()-1 then 'Yesterday'
- 10.5K All Categories
- 6 Connect
- 916 Connectors
- 250 Workbench
- 463 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 476 Datasets
- 189 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 78 Cards, Dashboards, Stories
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 172 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive