9 Replies Latest reply on Feb 13, 2019 12:06 PM by Tim Dines

Date formatting incorrectly when connecting to database

Philip Kanis Feb 4, 2019 11:25 PM

Hi,

I'm trying to connect to a Microsoft SQL Server database however the date fields are coming through as a string and when I convert the type to "Date" they are being read as YYYY-DD-MM instead of YYYY-MM-DD. This results in a large amount of null dates as it excludes any date where the DD is above 12 (as it is reading it as MM).

My Operating System region is correct (YYYY-MM-DD) and this is for 10.1 Desktop

Thank you.

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)
Like Show 1 Likes(1)

Actions
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.
Like Show 0 Likes(0)

Actions
3. Re: Date formatting incorrectly when connecting to database

Nick Parsons Feb 6, 2019 3:32 PM (in response to Philip Kanis)

Can you post a workbook (twbx) with this extract?
Like Show 1 Likes(1)

Actions
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.
Like Show 0 Likes(0)

Actions
5. Re: Date formatting incorrectly when connecting to database

Norbert Maijoor Feb 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
Like Show 1 Likes(1)

Actions
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.
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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"
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions