6 Replies Latest reply on Dec 12, 2017 10:03 AM by Hari Ankem

# Easiest way to convert string numbers to date?

Howdy All!

I am working with some SQL and wrote the code to pull Year/Month:

DATE_PART ('year', Date) || DATE_PART ('month', Date) AS YEARMONTH

Now tableau will read it as strings: 20178, 20179, 201710, 201711, etc.

I tried using a DATEPART and DATEPARSE YYYYMM but it doesn't work as many months do not have the leading zero. Example being 20178 vs 201708.

I am quite honestly not sure if this is easier to do in SQL or now in Tableau but I wanted the results to be dates such as, August 2017, September 2017, etc.

Is there an easy way to accomplish this? I attached a worksheet below with the issue.

Thanks for any assistance!

• ###### 1. Re: Easiest way to convert string numbers to date?

Hi,

couldn't open your attached workbook, however tried your calculation on sample data.

write a calculation like this:

DATE(LEFT(STR([Year]*100+[Month]),4)+'-'+RIGHT(STR([Year]*100+[Month]),2)+'-01')

Please find attached workbook, hope that helps.

• ###### 2. Re: Easiest way to convert string numbers to date?

While I am not commenting on what is easier, you can prefix a zero for the numeric month number less than 10 in your SQL query itself.

DATE_PART ('year', Date) ||

CASE WHEN DATE_PART ('month', Date)<10 THEN "0" ELSE "" END ||

DATE_PART ('month', Date) AS YEARMONTH

I am not sure which database you are connected to. So, the syntax may differ but I think you get the idea.

• ###### 3. Re: Easiest way to convert string numbers to date?

Howdy Sunil!

There might be a similar way to do it but currently my data already has year and month as a string.

So it is slightly different in my case because where you are using 2 fields in the calculation mine is pulling from 1. Is there a way to accomplish the calculation from this 1 field?

I could change the SQL to pull year and month separate but just from learning perspective I'd like to see how it is done on a YEARMONTH field.

Also Hari I think that is a cool idea for the SQL! I will give that an attempt when I can.

• ###### 4. Re: Easiest way to convert string numbers to date?

Try this out:

• ###### 5. Re: Easiest way to convert string numbers to date?

It was a slow response from me but this worked! It was very cool seeing that Hari, thank you!

• ###### 6. Re: Easiest way to convert string numbers to date?

You are welcome.