13 Replies Latest reply on May 30, 2013 12:20 PM by Ryan Polk

# Converting Integer to Date

I need to convert an 8 digit integer date field named SDYMD (YYYYMMDD) into a Date field. I have attempted to use this calculation:

Date(char(34)+mid(str([SDYMD]),5,2)+"/"+right(str([SDYMD]),2)+"/"+left(str([SDYMD]),4)+char(34))

SDYMD is the integer field in my data table.

I am receiving this error message from Tableau:

"All fields must be aggregate or constant when using table calculation functions or fields from multiple data sources."

How should I make this conversion?

• ###### 1. Re: Converting Integer to Date

It should work. Can you post a tbwx with a small sample of the data? Do you have more than one data source?

• ###### 2. Re: Converting Integer to Date

Try to avoid string calculations if you can. Since your 'date' is an integer, it' best to use numerical operators...

DATEADD('month', INT(([SDYMD] % 10000) / 100) - 1,

DATEADD('year', INT([SDYMD] / 10000) - 1900,

#1900-01-01#)))

3 of 3 people found this helpful
• ###### 3. Re: Converting Integer to Date

Nice way to do it Robin.

I would love to see a comparison of when the number of rows makes a significant difference when using strings.

• ###### 4. Re: Converting Integer to Date

Richard Wesley gave a presentation at TCC12 stating that on 1 billion rows the calculation using strings took 5 hours vs 64 seconds using mathematical operations!

• ###### 5. Re: Converting Integer to Date

Interesting - assuming a linear function (not necessarily true..) then 100,000 rows would take 1.8s with string functions vs 0.01. There's definitely a break point somewhere around there for me for time vs. ease of writing string based calcs.

• ###### 6. Re: Converting Integer to Date

So this is interesting - I created a 100,000 row excel file with randomly generated dates (ended up being 3,648 unique dates) to match the OP's format, his original calc (minus the char()s) and yours. Connected these to Tab (non-extracted), saved the workbook and exited.

Reopen, turn on perf analyzer, drag one of the calcs using the right mouse button to rows, and choose discrete. Wait for execute completion and turn off analyzer. Close again without saving, and repeat with the other calc. Repeat both several times.

The string function takes right around 17s to execute. The numerical function takes 32-36 seconds to execute, so it's not clear that numerical is always best. Now this may scale differently (next project), but for 100,000 rows, non-extracted JET connection, and this display, string is faster. This of course is only for this particular scenario.

Edit: BTW after the display ends up in memory, both are extremely quick - hundreths of a second to execute

• ###### 7. Re: Converting Integer to Date

Hmm... just tried a similar experiment with Excel and got the same sort of results... that's odd. String calcs are most definitely harder for a machine to compute normally...

When you switch to extracts there's zero difference because the extract gets optimized and the calcs are put into the source...

I blame Microsoft.

• ###### 8. Re: Converting Integer to Date

I think that's a fair assessment... We need to try this with a SQL source, and up the rows into the millions in both scenarios.

• ###### 9. Re: Converting Integer to Date

I am connected to a BigQuery data source. How do I load a small amount of data into at tbwx?

• ###### 10. Re: Converting Integer to Date

Thanks for the idea. I tried your calc and receive this error message:

"DATEADD is being called with (string, float, datetime) did you mean (string, integer, datetime)?"

How do I correct this?

Thanks again,

Ryan

• ###### 11. Re: Converting Integer to Date

Robin,

I made a couple of mods and the problem is now solved! Thanks again for the idea!!

Final calc:

DATEADD('month', int((([SDYMD] % 10000) - ([SDYMD] % 100)) / 100) - 1,

DATEADD('year', INT([SDYMD] / 10000) - 1900,

#1900-01-01#)))

• ###### 12. Re: Converting Integer to Date

Hi sir,

Plase send tableau materail.

Thanks,

Kesava

• ###### 13. Re: Converting Integer to Date

A.kesava reddy:  The last reply in this thread was on May 30, 2013.  Just making sure you've seen this.