You can use the DateParse function to convert the string to date in the format specified.
DATEPARSE ("dd.MMMM.yyyy", "15.April.2004") = #April 15, 2004#
DATEPARSE ("h'h' m'm' s's'", "10h 5m 3s") = #10:05:03#
Follow the link for further information
Thanks for the suggestion - but I don't have acces to DATEPARSE. I'm connecting to a SQL Server - does that make a difference?
I am not sure if it works differently when connected to SQL Server.
Experts out in the forum may be able to suggest further.
DATEPARSE should be available regardless of your data connection.
Why not just click on the dimension and change the datatype to Date & time ? Does that return an error at all ?
If I connect your Excel Data Sheet, and then perform the DATETIME calculation to the 'According to data base' field, it seems to calculate all the values as dates...very odd!
As a last resort, if it still isn't working you could rebuild the date up (old Excel style) using DATE(INT(RIGHT([date as String],4)),INT( MID([date as string],4,2)),.....
DateTime 2.twbx 235.4 KB
I get a null value if I change the datatype to Date and Time. I tried that first.
I would like to add, what is the data type in SQL Server? *char* or *date*?
Ps. I assume the connection is directly to SQL Server.
I guess i am late to the party. Can you add double quotes to the value and use datetime function.
Unfortunately dateparse does not support all datasources. Chexk this snippeet from Tableau documentation:
"The DATEPARSE function is available for connections to MySQL, Oracle, PostgreSQL, and Tableau data extracts. The DATEPARSE function takes two parameters, “format” and “string.”
I have the same problem and I found this:
If you don't need a live connection and can afford to have an extract, then you should be able to apply DATEPARSE.
1) What Regional Settings are on your local computer OS?
I suppose something like English (United States).
Could you change it (temporarily) to something like English (Canada) or so?
You may expect what I'm going to :-)
2) What version of Tableau Desktop are you using?
If 8.2 or 8.3 then you're lucky. Simply open your wb
and change your field in question to a Date & time type,
as Chris recommended. Don't worry about Nulls (yet).
Chances are the dates would be looking better than before.
3) If you're on a v9 already, then changing OS locale is of no help.
Or not? Please verify.
I have no MS SQL Server at hand right now (it's sunday morning),
but I could make a quick POC with Tableau v8 & v9 Text/Excel connectors.
Please find the attached workbooks. One could easily see a difference
when opening both v8 & v9 workbooks simultaneously and making a quick exercise
by changing OS Regional Settings and refreshing a view.
Hope it would help a bit.