I have my workbook pulling from a few SQL tables. The table that contains the date and time have them split into 2 seperate columns (see below). When I use as is it's trying to read the date and will not return a time at all.
How would I go about formatting the time column in Tableau to only include the time?
Thank you for your help and apologies if this has been asked before.
You could create a calculated field and use the following:
RIGHT(STR(MAKETIME(INT(DATETRUNC('hour',[Your Field])),INT(DATETRUNC('minute',[Your Field]))
,INT(DATETRUNC('second',[Your Field])))),8)
I think the answer to your question depends on what you want to do with the time field. If you need to add it to other dates, etc, then leaving the prefix date won't be a terribly bad idea because what that date really represents is a date=0. Regardless of how it shows up in the table, for the purposes of formatting, you always have the latitude to change the formatting of what's shown, so if you only need it for labeling, you still don't need to worry about how it looks on the table. Just right click on the field and go to default properties>Date Format> and make it look like you want it to. That only effects the default mask that is applied when you drag it onto a view, which you can customize however you want using the date format codes : https://onlinehelp.tableau.com/current/pro/desktop/en-us/dates_custom_date_formats.html
Further on formatting: if you want the data to show up differently from the default on a single view, you can click on the pill in the view and select format and you have a similar dialog there to change how it's formatting just for that pill on that worksheet.
e.g.
Thanks for the info Justin. That did help me be able to change the time formatting on visualizations, but not for the date field. I wanted to explain my issues a little more in depth, because this may be similar to Tim's initial post.
Photo 1 is the formatting I was able to do for time (like you pointed out in your post), but not date. See annotation mark and usagedate legend.
Photo 2 is the error I get when simply trying to make a group of 4 time intervals (example: 4:00pm on 4 different days in a year on the graph above). I need to be able to do this and reference specific time intervals independently (or a group like I was trying to create) in calculated fields. When I try to reference an interval, I get a a similar error.
Same workbook: I have an local Excel file I am trying to blend with my connected SQL data. I used an "Hour Part" comparison to solve the time incompatibility, but usagedate is still incompatible between the two data types. Photo 3 shoes inner join successful on Hour Part, and photo 4 shows an error when I try to compare Excel date format with Tableau/SQL date format of MM/DD/YYYY 12:00AM
These are the types of issues the SQL format is causing me, which is why I tried Brian's formula, hoping to extract the date/time that is combined in both fields separately but that did not work either.
I'd appreciate any guidance!! Please let me know if you have any questions.
hmm, yes, the TIME data type in SQL Server is not used a ton in my experience.
I feel like all of your issues would actually just be resolved if you merge the two fields in your SQL, then parse as necessary from Tableau with trunc or datepart.
In SQL, if you customize your SQL, you can get rid of the two fields, and replace them with this single one:
cast(UsageDate as datetime)+ cast(UsageTime as datetime) As UsageDatetime
Alternatively, leave them separate and just cast the UsageTime as Datetime instead of TIME data type, then try whatever operation you were already trying - that would resolve the "incompatible" error SQL was throwing.