-
1. Re: Date returning null
Simon RuncDec 31, 2014 3:47 AM (in response to Kirstin Lyon)
hi Kirstin,
Not sure why that's not working, in the attached I've added the STR() conversion, to ensure the Datetime function is passed a string.
Hope this helps.
-
DateTime.twbx 10.5 KB
-
-
2. Re: Date returning null
Neeraj Rajpal Dec 31, 2014 4:08 AM (in response to Kirstin Lyon)Hi,
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
-
3. Re: Date returning null
Kirstin Lyon Jan 4, 2015 10:13 PM (in response to Neeraj Rajpal)Hi Neeraj,
Thanks for the suggestion - but I don't have acces to DATEPARSE. I'm connecting to a SQL Server - does that make a difference?
Thanks
Kirstin
-
4. Re: Date returning null
Neeraj Rajpal Jan 4, 2015 10:19 PM (in response to Kirstin Lyon)Hi Kirstin,
I am not sure if it works differently when connected to SQL Server.
Experts out in the forum may be able to suggest further.
-
5. Re: Date returning null
Kirstin Lyon Jan 4, 2015 10:24 PM (in response to Simon Runc)Hi Simon,
Unfortunately, it didn't solve the problem.
I've attached an excel sheet with the date, then what Tableau finds - sometimes it's ok, other times not.
Thanks
Kirstin
-
Data.xlsx 234.6 KB
-
-
6. Re: Date returning null
Chris McClellanJan 4, 2015 10:30 PM (in response to Kirstin Lyon)
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 ?
-
7. Re: Date returning null
Simon RuncJan 5, 2015 1:37 AM (in response to Kirstin Lyon)
hi Kirstin,
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
-
-
8. Re: Date returning null
Kirstin Lyon Jan 5, 2015 2:02 AM (in response to Chris McClellan)
Hi Chris,I get a null value if I change the datatype to Date and Time. I tried that first.
Thanks
Kirstin
-
-
10. Re: Date returning null
kettan Jan 5, 2015 4:44 AM (in response to Shawn Wallwork)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.
-
11. Re: Date returning null
KK Molugu Jan 5, 2015 5:22 AM (in response to Kirstin Lyon)Kristin.
I guess i am late to the party. Can you add double quotes to the value and use datetime function.
datetime("22-12-2014 14:19:50")
..kk
-
12. Re: Date returning null
KK Molugu Jan 5, 2015 5:27 AM (in response to Chris McClellan)Chris.
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.”
..kk
-
13. Re: Date returning null
vincenzo.deconcilio Apr 17, 2015 3:43 AM (in response to Kirstin Lyon)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.
Cheers
-
14. Re: Date returning null
Yuriy FalApr 18, 2015 7:25 PM (in response to Kirstin Lyon)
Hi Kirstin,
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.
Yours,
Yuri
-
Date_returning_Null_v9.twbx 277.2 KB
-
Date_returning_Null_v8.twbx 276.8 KB
-