10 Replies Latest reply on Sep 16, 2016 11:08 PM by Richard Kim

    Convert Week String to Week Number,Oracle database error 1843: ORA-01843: not a valid month

    Richard Kim

      Hello,

       

      I am having difficulty converting a string date to a number date and SQL is not my strong point. 

       

      When I tried connecting Tableau to an excel flat file, the following equation worked perfectly (with the exception of allowing me to filter year properly) from this discussion

       

      Hi Everyone,How to convert week number to date using calculated field in tableau?

       

      DATEADD('week',INT(MID(STR([Week]), 5,2))-1,DATE("01/01/"+(MID(STR([Week]), 1,4))))

       

      However, now that I am connected to Oracle, I get the following error using the same equation,

       

      'Oracle database error 1843: ORA-01843: not a valid month'

       

      My weeks look like this,

       

       

      WeekNum
      201514
      201515
      201616
      201617
      201618


      Ideally, I would like to filter on a week while keeping it within the same year.  Any and all help would be of great assistance!!

       

       

      I've tried other discussions (below) in the community but do not have a strong grasp of how all the equations work,

       

      convert string week number to date week?

      Converting Year(string) to Date format in Oracle

       

      Thanks!