Tableau DateAdd in Custom SQL Data Connection

//

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!

List of MS SQL DatePart abbreviations

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:

DATEADD('ww',NumWeeksToAdd,DateToAdd)

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.

3 thoughts on “Tableau DateAdd in Custom SQL Data Connection”

  1. Hi,

    Please let me know, how we can write this Sql statement in custom sql using tableau.

    CASE WHEN DATENAME(dw, StartDate) = ‘Sunday’ THEN 1 ELSE 0 END

    Thanks

    Reply
  2. Hi Awatson,

    Thank you for your reply.

    I am not able to find equivalent query for this in custom Sql. Please help on this.

    SELECT
    (DATEDIFF(dd, @StartDate, @EndDate) + 1)
    -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
    -(CASE WHEN DATENAME(dw, @StartDate) = ‘Sunday’ THEN 1 ELSE 0 END)
    -(CASE WHEN DATENAME(dw, @EndDate) = ‘Saturday’ THEN 1 ELSE 0 END)

    Please let me know, the site or forum where we can find related queries on custom sql in tableau.

    thank you again

    Poornima

    Reply

Leave a Reply to awatson Cancel reply