-
1. Re: Date formatting incorrectly when connecting to database
Mihai Constantinescu Feb 5, 2019 1:48 AM (in response to Philip Kanis)You could format the date dimension as below or create a calculated field with DATEPARSE function (which I believe is better in your case as the string is wrongly parsed). Create a new DATE dimension from the string using the DATEPARSE function which allows you to format that string as you wish e.g. DATEPASE("dd.mm.yyyy", your_date_string)
-
2. Re: Date formatting incorrectly when connecting to database
Philip Kanis Feb 6, 2019 2:25 PM (in response to Mihai Constantinescu)Hi Mihai,
Thanks for the reply.
Unfortunately as this a live extract the Dateparse function is not available (similarly neither is MAKEDATE() )
I've tried creating a column with a DATE(LEFT MID etc.) calculated field but this also produces the same null values.
-
3. Re: Date formatting incorrectly when connecting to database
Nick ParsonsFeb 6, 2019 3:32 PM (in response to Philip Kanis)
Can you post a workbook (twbx) with this extract?
-
4. Re: Date formatting incorrectly when connecting to database
Philip Kanis Feb 10, 2019 10:17 PM (in response to Nick Parsons)Hi Nick,
I cant do that unfortunately, sensitive information.
-
5. Re: Date formatting incorrectly when connecting to database
Norbert MaijoorFeb 11, 2019 11:43 AM (in response to Philip Kanis)
Hi Philip,
Upfront;) I don't have any experience with Tableau on Microsoft SQL Server database but find my approach as reference below and stored in attached workbook version 10.5 located in the original thread.
1. D1. Date Converted: DATE(DATEPARSE ( "dd-MM-yyyy", mid([Date],6,2)+'-'+mid([Date],9,2)+'-'+mid([Date],1,4) ))
Hope it helps
Regards,
Norbert
-
Dateparse_nalmai v10.5.twbx 143.6 KB
-
-
6. Re: Date formatting incorrectly when connecting to database
Tim Dines Feb 11, 2019 11:52 AM (in response to Mihai Constantinescu)A couple of things. If your data string has dots in it, convert them to "/". If you are getting string that is just numbers then you could just parse the string and put the numbers in the correct order before calling the DATE function.
-
7. Re: Date formatting incorrectly when connecting to database
Philip Kanis Feb 12, 2019 9:22 PM (in response to Norbert Maijoor)Thanks for the info Norbert, unfortunately I cannot use the DATEPARSE function as this is an Extract
-
8. Re: Date formatting incorrectly when connecting to database
Philip Kanis Feb 12, 2019 9:22 PM (in response to Tim Dines)Hi Tim, the string is formatted "YYYY-MM-DD"
-
9. Re: Date formatting incorrectly when connecting to database
Tim Dines Feb 13, 2019 12:06 PM (in response to Philip Kanis)If you are using a custom SQL statement to get the data from SQL Server then you can reformat the date in the statement before it comes into Tableau. If you are not then I have done this before, but it is tedious. I break the string down into three fields called MM, DD and YYYY and then created a fourth calculated field to assemble them the way I want them. I am sure there are better ways, but I have done it this way in multiple environments for a long time and it is just something I do at this point.