Skip to content

    • Magic ETL

    Magic ETL

    how do you format date for comparison to today

    Member
    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Comments

    • 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!

    • Member

      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

    • Contributor

      Have you tried without formatting the call_date? 

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

      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
    • Member

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

    • Member

      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 '='

    • 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

       

      1. 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'
    • Contributor

      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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories