9 Replies Latest reply on Jan 22, 2014 8:49 AM by Joshua Milligan

# Converting to a Date/Time Field

I'm working with frequency and severity data and the 'time' field we have stored is the year followed by a decimal representing the month divided by 12.

So they the fields take the form of:

1986.00000000000

1986.08333333333

1986.16666666667

1986.25000000000

etc.

How would I convert them to date/time field so I can create some xy plots?

The standard coversion lumps everything into 1905.  Do I need to preprocess the field outside of Tableau?

Thanks!

Mike

• ###### 1. Re: Converting to a Date/Time Field

Hi Mike,

Check these following link. These may help you to understand the concept of date format in Tableau.

Formatting Numbers and Dates | Tableau Software

Correcting Date Fields that Tableau Interprets as Strings | Tableau Software

Supported Date Formats | Tableau Software

Also, please post your sample workbook so that we can understand your dataset & will suggest you according to that.

Warm Regards,

Prashant Sharma - India | LinkedIn

• ###### 2. Re: Converting to a Date/Time Field

Here's a sample:

 Trend Co State Year Month Time MaxOfTime Tot TotChg BI BIChg PD PDChg Coll CollChg MP MPChg Comp CompChg PIP PIPChg Weath WeathChg PaidFreq A 9 1987 1 1987.083 2014 0 0 3.269321872 0.66586201 5.91558377 0.9154192 6.40046309 1.33832676 2.55376213 1.23416564 12.4572791 0.71922988 0 0 14.07444 0.99941831 PaidFreq A 9 1987 2 1987.167 2014 0 0 3.875510323 1.18541718 6.23743272 1.05440696 7.64738222 1.19481702 2.50955457 0.98268924 12.218318 0.98081755 0 0 14.30297 1.01623757 PaidFreq A 9 1987 3 1987.25 2014 0 0 4.886327292 1.26082164 4.96033439 0.79525257 6.81285789 0.89087451 2.70656269 1.07850322 11.8725847 0.97170369 0 0 14.92196 1.04327648 PaidFreq A 9 1987 4 1987.333 2014 0 0 2.92032899 0.59765317 5.28795749 1.06604859 7.40184445 1.0864522 2.95312182 1.09109678 10.7859175 0.90847257 0 0 13.88222 0.93032161 PaidFreq A 9 1987 5 1987.417 2014 0 0 4.811760189 1.64767744 4.69018736 0.88695633 6.92974636 0.93621886 3.39418232 1.14935398 12.9857935 1.20395817 0 0 13.20615 0.95129996 PaidFreq A 9 1987 6 1987.5 2014 0 0 4.011117411 0.83360709 4.16948523 0.88898053 6.2234811 0.89808209 3.12908581 0.9218968 10.0993567 0.7777235 0 0 12.05618 0.91292168 PaidFreq A 9 1987 7 1987.583 2014 0 0 2.895522956 0.7218744 5.55951465 1.33338154 5.27852325 0.84816249 2.66857116 0.85282773 12.8307276 1.27044999 0 0 11.70903 0.97120546 PaidFreq A 9 1987 8 1987.667 2014 0 0 4.148545211 1.43274471 6.14359296 1.10505923 5.2476316 0.99414767 2.38357067 0.89320109 13.4527434 1.04847861 0 0 10.8608 0.92755732 PaidFreq A 9 1987 9 1987.75 2014 0 0 4.565872057 1.10059595 4.10335735 0.6679084 6.62008377 1.26153745 3.1074656 1.30370189 12.700668 0.94409502 0 0 10.47456 0.96443756 PaidFreq A 9 1987 10 1987.833 2014 0 0 3.296498285 0.72198657 6.46129952 1.57463729 5.62728736 0.85003265 3.84538445 1.23746646 10.7168638 0.84380316 0 0 9.934545 0.94844497 PaidFreq A 9 1987 11 1987.917 2014 0 0 3.422761793 1.03830231 5.24957685 0.81246456 7.79043978 1.38440412 3.09141758 0.80392939 9.93516335 0.92705885 0 0 10.68894 1.07593664 PaidFreq A 9 1987 12 1988 2014 0 0 2.531949248 0.73973867 4.80839416 0.91595843 6.41742517 0.82375647 2.50925563 0.81168447 13.1145142 1.32000993 0 0 10.00749 0.93624712 PaidFreq A 9 1988 1 1988.083 2014 0 0 3.755704631 1.4833254 5.21039938 1.08360488 6.12481864 0.95440437 3.38405585 1.34862937 9.43235044 0.71922988 0 0 9.998487 0.99910035 PaidFreq A 9 1988 2 1988.167 2014 0 0 2.500781024 0.66586201 6.72716092 1.29110274 6.8144444 1.11259529 2.62750055 0.77643534 10.8311331 1.1482963 0 0 10.26344 1.02649911 PaidFreq A 9 1988 3 1988.25 2014 0 0 3.068689739 1.22709254 6.86531833 1.02053725 5.47218339 0.80302708 3.2427709 1.23416564 9.32894314 0.86130814 0 0 9.757966 0.95075016 PaidFreq A 9 1988 4 1988.333 2014 0 0 3.784685316 1.2333229 6.28464421 0.9154192 7.9079241 1.44511314 2.01900794 0.62261813 9.29676841 0.99655108 0 0 9.496691 0.97322445 PaidFreq A 9 1988 5 1988.417 2014 0 0 4.688379308 1.23877652 4.82370486 0.76753826 5.59323133 0.70729451 2.38778826 1.18265422 11.9187702 1.28203368 0 0 9.348966 0.98444464 PaidFreq A 9 1988 6 1988.5 2014 0 0 3.465017301 0.73906505 6.93058873 1.43677711 6.09753464 1.09016314 2.84504116 1.19149642 10.8221063 0.9079885 0 0 9.839605 1.05248054 PaidFreq A 9 1988 7 1988.583 2014 0 0 2.807951166 0.81037147 6.88437476 0.99333188 5.48920693 0.90023382 2.31661042 0.81426253 9.16868399 0.84721807 0 0 10.0799 1.02442135 PaidFreq A 9 1988 8 1988.667 2014 0 0 4.074903192 1.45120159 4.3210667 0.62766291 5.22845696 0.9524977 2.03865565 0.88001661 9.55288771 1.04190391 0 0 10.12967 1.00493758 PaidFreq A 9 1988 9 1988.75 2014 0 0 4.011336109 0.98440035 4.19281767 0.97032005 6.99738386 1.33832676 3.4619944 1.69817517 9.85226873 1.03133932 0 0 10.65339 1.05170115 PaidFreq A 9 1988 10 1988.833 2014 0 0 2.878880632 0.71768622 6.87261993 1.63914114 6.95065775 0.99332235 3.96252479 1.14457863 13.2600307 1.34588602 0 0 11.36397 1.06670054 PaidFreq A 9 1988 11 1988.917 2014 0 0 4.699950681 1.63256185 5.86278463 0.853064 7.19039184 1.03449085 3.2302248 0.81519359 9.36300594 0.70610741 0 0 11.28325 0.99289651 PaidFreq A 9 1988 12 1989 2014 0 0 2.866466207 0.60989283 4.37091415 0.74553551 5.13906508 0.7147128 3.98175657 1.23265618 11.7474154 1.25466282 0 0 11.27669 0.99941831
• ###### 3. Re: Converting to a Date/Time Field

Mike,

Try something like tihs:

DATEADD('month', ROUND(12 * ([Date] - INT([Date]))), DATE("1/1/" + STR(INT([Date]))))

This will start with a date of January 1st for the Year and then add the months based on the decimal places.  I resorted to using ROUND as it seems that anything that wasn't quite the next whole number was being cast as an INT and therefore just truncating instead of rounding.

Regards,

Joshua

• ###### 4. Re: Converting to a Date/Time Field

Awesome, thanks for the help!

• ###### 5. Re: Re: Converting to a Date/Time Field

This should be more performant than the other options, because it's completely avoiding string conversions:

It's based on this post Re: Converting Integer to Date by Robin Kennedy, which was presented by Richard Wesley at TCC12.

• ###### 6. Re: Re: Converting to a Date/Time Field

Thanks Jonathan!  That one belongs in the TCL.

-Joshua

• ###### 7. Re: Re: Converting to a Date/Time Field

Jonathan,

You might take a look at this if you get a chance:

There appears to be a slight rounding/truncation issue.  For the date: 1986.08333333333

gives the result 1/1/1966

but

gives the result 2/1/1986

I suspect the DATEADD function is implicitly casting the argument to an INT causing a truncation issue (.083333333 *12 is .999996 after losing some precision).  Do you know if this is expected behavior?

Regards,

Joshua

• ###### 8. Re: Re: Converting to a Date/Time Field

Hi Joshua,

Where I'd started is with the sample data Mike provided, and in the first row of that 1987.08333 is month 1, I assumed that should be 1/1/1987 and not 2/1/1987, which is a different result than your calculation. But I messed up with the rounding, thanks for pointing that out. To get back to what I think are Mike's results, I added a -1 offset to the Month DATEADD: