how do you format date for comparison to today

user11351
user11351
in Magic ETL

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

Comments

  • Cartergan
    Cartergan Contributor

    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
    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
    Cartergan Contributor

    Have you tried without formatting the call_date? 

    CASE
    WHEN 'call_date' = CURDATE() THEN 'Today'
    WHEN 'call_date' = CURDATE()-1 THEN 'Yesterday'
    END
  • cwolman
    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.


    -----------------
    Chris
  • user11351
    user11351

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

  • user11351
    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
    Godiepi Coach

    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

    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'
  • cwolman
    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


    -----------------
    Chris

Categories