Unfortunately, no. Most of the questions were left unanswered. Some had different methods, but they did not work for me.
This method seemed to be the most promising, but I could not make it work for me:
Thank you for the quick response, by the way.
Try DATE(TRIM([Year]) + "-01-31")
(I like putting day over 12 to minimize confusion between month and day - makes it easier for Tableau to dismiss certain format strings as we go though fair amount of variations to decide which one fits the best)
Basically this comes down to following logic:
- When dealing with Hive, Tableau uses Hive's TO_DATE() function when handling Tableau's DATE() calculation
- In the documentation it is stated that TO_DATE() needs timestamp data type as an input argument
- Since Tableau sends a string instead of a timestamp - that string needs to be in a proper format (and only in that format) n order for implicit conversion to be successful
- Here's a snippet from documentation:
... automatically converts STRING literals of the correct format into TIMESTAMP values. Timestamp values are accepted in the format YYYY-MM-DD HH:MM:SS.sssssssss, and can consist of just the date, or just the time, with or without the fractional second portion. For example, you can specify TIMESTAMP values such as '1966-07-30', '08:30:00', or '1985-09-25 17:45:30.005'. You can cast an integer or floating-point value N to TIMESTAMP, producing a value that is N seconds past the start of the epoch date (January 1, 1970).
So, in order for conversion to be successful, date string would have to be rearranged to match the timestamp format. Or better yet, stored in the table in proper timestamp'ed form.
This worked like magic. I do not know very well how Hive works, so I could not find a solution myself. I really appreciate the time you put to solve my problem and add the detailed explanation on how it works.
Thank you so much.