1 Reply Latest reply on Dec 13, 2016 1:00 PM by Santiago Sanchez

    Problem creating a Datetime field

    Nate Mara

      I have an existing Date field that is a proper Tableau Date, and I'm trying to combine it with a time field that is stored as an integer in 24H format (724 = 7:24 AM, 1932 = 7:32 PM). The query that wrote is

       

      Datetime = MAKEDATETIME([Date], DATEPARSE("Hmm", STR([Trans Time])))

       

      This seemed fine, and the query editor did not find any problems with it, but when I tried to add it to a worksheet, I got the following error message:

       

      An error occurred while communicating with data source 'textscan.abunchofnumbersandletters'.

       

      If I click on 'Show Details', I get a slightly more descriptive error message:

       

      Tableau Data Engine Error: 48032: no such function (+ date datetime)

       

      And if I open the full query, I see some interesting lisp-like code, I'll leave only the relevant part:

       

      (year (+ [Date] (parsedate (to_str [TRANS_TIME]) "HHmm")))

       

      The relevant code appears to create the Datetime object from the string, and instead of calling an internal MAKEDATETIME function, the Date and Datetime objects are just added together. Am I missing something about the way Date/Datetime objects work here, or is there a better way to do this? Thanks!

        • 1. Re: Problem creating a Datetime field
          Santiago Sanchez

          Hi Nate,

           

          You almost have it! MAKEDATETIME receives 2 values: a date and a time. On the example above, while the first value you sent to the function is a date, the second one is also a date (DATEPARSE returns a date). Instead, we need to send a time for the second value. Here's one way to do it:

           

           

          MAKETIME is creating a time value from [Trans Time]. Since [Trans Time] could have either 2 or 3 characters, we need to keep that in mind when selecting the hours (take either 1 or 2 characters from the left, depending on the length of the string). Minutes are easier, they are always the last 2 characters. And we can set 0 for seconds. Here's a function you can copy:

           

          MAKEDATETIME([Date],

              MAKETIME(

                  INT(LEFT(STR([Trans Time]), LEN(STR([Trans Time])) -2)), //hour

                  INT(RIGHT(STR([Trans Time]), 2)), // minute

                  0) // second

          )

           

          Hope this helps!