The syntax of DATEADD in Tableau Custom SQL differs to the syntax of DATEADD in a Tableau calculated field.
This can and does cause confusion!
Naturally one would expect the syntax to be consistent, however, Tableau Custom SQL uses MS Jet. Therefore, anything written in Custom SQL has to follow MS Jet SQL syntax.
DATEADD syntax to add weeks in Tableau Custom SQL
The syntax of DATEADD(UnitToAdd,NumberToAdd,DateToAdd).
For example to adds weeks in a Tableau calculated field use:
DATEADD('week',4,#1 Jan 2011#) = #29 Jan 2011#
Using the same formula in the Custom SQL area of a Tableau data connection throws an error.
This is because CustomSQL uses MS Jet SQL syntax, not Tableau syntax.
Use MS Jet syntax in Tableau Custom SQL
MS Jet SQL doesn’t recognise ‘Week’ in the DATEADD function.
The week in MS Jet SQL syntax is ‘ww’ or ‘wk’.
Therefore, for DATEADD to work in Tableau CustomSQL, the syntax needs to be:
The full DATEADD SQL syntax is available on the MS website at this link: http://msdn.microsoft.com/en-us/library/ms174420.aspx
Note: SQL syntax doesn’t work in a Tableau calculated field; use Tableau syntax in a Tableau calculated field.
Click here for Tableau date syntax.
Custom SQL in Tableau differs to many other forms of SQL. Some of the differences are described in this article explaining some of the quirks of Tableau Custom SQL.