There are very likely better ways to do this, but maybe this can give an idea.
Basically, you can use DATEADD to build up what you want.
I had two fields in Excel: Date (20180913) and Time (12:35:00)
Maybe because I was using Excel, Tableau pulled the Time in as 12/30/1899 12:35:00.
Whatever date value that gets pulled in for your Time field, you can use that as a base.
This was the calculated field that I used:
DATEADD ( 'day', ( INT ( RIGHT ( [Date], 2 ) ) - 30 ),
DATEADD ( 'month', INT ( MID ( [Date], 5, 2 ) ),
DATEADD ( 'year', ( INT ( LEFT ( [Date], 4 ) ) - 1900 ), [Time] ) ) )
Please see workbook v10.0 attached in the Forum Thread.
281895dt.twbx 11.1 KB
Hello SWAROOP.GANTELA, thank you so much for the help you have provided me.
I understand the logic, but I'm a little bit confused because my two fields are different than yours.
I see your fields are like this:
Date = '20180913'
Time = '30/12/1899 12:35:00'
But in my case the fields are not related between the date and time field.
Date = '20180914'
Time = '12:30:00'
And is all in a string datatype.
When I try the solution you provided in the last post, Tablaeu gives me an error expecting another format or datatype maybe?
I tried to concatenate [Date] +" "+ [Time] but it does not work.
I have attached an example so you can know what I'm talking about.
Have a great day.
PD: Just for the records, the odbc error I meant before, when I try to convert Time (in string) as a "Date and time" datatype, it gives an error saying the field is not valid.
2 of 2 people found this helpful
Ah, I wasn't sure how it would come through on your side.
Here is an extension of the same concept:
Apologies, I don't immediately have access to 9.3
Thank you for the time you've spent swaroop.gantela.
The thing is I believe this only works with data gathered by excel and so.
In my case, the iSeries DB2 database pops up an error with the odbc driver, I believe this is because on the other side is expecting to receive the same datatype when is live update and not extract.
Each time I use the calculated field with dateadd function it gives me an error with the ODBC driver.
Anyway, your answer is valid for other databases so I want to say thank you for your post.
Have a great weekend.
Was checking to see if you found resolution for your issue.
I saw another thread with a different approach and wondered if it may help you:
I also saw that my previous attempt may have been wrong.
Maybe something along the lines of this is better:
DATEADD('month', INT(MID([Date Sec],5,2)) - 1, // note the -1 needed here
This is from this thread: