Dateparse MON in Tableau

2
votes

I have a data source that returns a date as a string in the form of 'MON YYYY' (APR 2014, MAY 2014, etc.).

I tried making a calculated field off of this information with the following formula:

DATEPARSE('MMM YYYY', [Field1])

This is a sample set of the data I'm getting (I added the pipe as a divider):

Field1 || Calculated Field

APR 2014 || 12/22/2013

APR 2015 || 12/28/2014

APR 2016 || 12/27/2015

AUG 2014 || 12/22/2013

AUG 2015 || 12/28/2014

AUG 2016 || 12/27/2015

I've also tried to add a day field, but that results in the same incorrect data as above:

DATE(DATEPARSE('dd MMM YYYY','01 ' +[Field1]))

Is there something I'm perhaps misunderstanding about the dateparse function?

1
tableau-api
what database or file format are you using to contain the data? - Alex Blakemore
Its an Oracle ODBC connection - mkingsbu
I was using a tableau extract, not a live connection if that makes a difference - mkingsbu
Just that some data sources don't support DateParse() but the ones you listed do, except perhaps ODBC - Alex Blakemore

1 Answers

2
votes

It turns out that YYYY means something totally different than yyyy. The capitalized MMMwas necessary for the MON type description. This worked for me:

DATE(DATEPARSE('MMM yyyy',[Field1]))

If you date the date off you'll get the hour, minute, second fields as well.

Dateparse converted it from a string [Field1] into a Date type using the aforementioned format of three digit month, a space, and a four digit year (e.g. AUG 2014 -> 8/2/2014).