CODEWITHSUNDEEP
tableau-api
Z Kay
Z Kay

Reputation: 23

Convert String to date in Tableau

I would like to convert string to date in Tableau. String looks like this:

Thursday, 26 June 2014 08:11:46 o'clock BST

I tried to use Dateparse, but I get Null values:

DATE(DATEPARSE("dd/MMMM/YYYY",[Posting Date]))

Upvotes: 2

Views: 797

Answers (2)

Stephen Berndt
Stephen Berndt

Reputation: 433

There isn't a simple one line way to do this that I could see. I made fields to find each of the date parts with regex_extract and then reassembled the parts with dateparse()

Date Day
REGEXP_EXTRACT([Value], '([0-9]{2})')

Date Month
REGEXP_EXTRACT([Value],'(January|February|March|April|May|June|July|August|September|October|November|December)')

Date Year
REGEXP_EXTRACT([Value], '([0-9]{4})')

Date Parsed
DATEPARSE("dd/MMMM/yyyy", [Date Day]+"/"+[Date Month]+"/"+[Date Year]

Here is an example

One word of caution is that regex does not work with all datasources in Tableau

Upvotes: 0

minatverma
minatverma

Reputation: 1099

you need to strip out extras like weekday name, o'clocks , timezone etc .

All these can be achieved simply as below regex . After that you can format as per dateparse

DATEPARSE("dd MMMM yyyy" , REGEXP_EXTRACT( [Date],'(\d+\s+\w+\s+\d+)' ))

explaining regex :

1. \d+ : extract one or more occurrence of numerical digits 
2. \s+ : extract one or more occurrence of space chars 
3. \w+ : extract one or more occurrence of alphabet chars 
4. \s+ : extract one or more occurrence of space chars 
5. \d+ : extract one or more occurrence of numerical digits

Upvotes: 2

Related Questions