3 Replies Latest reply on Apr 6, 2016 5:47 AM by Andrew Watson

    String to Date conversion returns Null

    Tsai Yin Yen

      I am connected live to Google BigQuery. I have a field [dayofpurchase] of string type and in the format of "DDMONYYYY: HH:MI:SS.000000". In Tableau, I created the following calculated field to get the format ready to be converted to date type:

      MID([dayofpurchase],3,1)

      +LOWER(MID([dayofpurchase],4,2))

      +" "

      LEFT([dayofpurchase],2)

      +", "

      +MID([dayofpurchase],6,4)

      +" "

      +MID([dayofpurchase],11,8)

      This displays fine as a string in the format "Mon DD, YYYY HH:MI:SS", but when I try to convert it using the DATETIME function, it returns Null values. I've tried a few variation of the date format, same result. Any idea? From reading and researching online, it seems like this might be a BigQuery issue.

      Running the following query in BigQuery: Select timestamp(dayofpurchase) from datatable; gave me Null values. Is there any workarounds?