xmetricFunctions

Tableau function Operators

Tableau + (date) to SQL

[d] + [b] moves a date-time forward by b days; a fractional number adds part of a day, so + 1.5 is 36 hours. In DuckDB, add an interval: d + to_days(b) for whole days, d + to_seconds(x * 86400) for fractions. A null on either side gives null.

Updated · Checked on the xmetric engine

What is the syntax?

date + number
date
a date or date-time.
number
days to add; may be negative or fractional.

Returns a date or date-time number days later. Null if either side is null.

What does it return, and what is the SQL?

Each example runs on the same 8 sample rows twice: the Tableau formula on the xmetric engine, and the SQL in DuckDB.

What is the date b days after d?

Tableau

[d] + [b]

SQL (DuckDB)

SELECT d + to_days(b) AS result
FROM input
ORDER BY id

Row 8 has b = -2, so it moves back two days.

bdResult
22024-02-28 18:002024-03-01 18:00
22024-02-29 00:002024-03-02 00:00
52024-03-01 09:302024-03-06 09:30
42025-01-01 12:002025-01-05 12:00
3null
22024-01-01 08:002024-01-03 08:00
2024-12-31 23:00null
−22024-06-15 10:002024-06-13 10:00

The Tableau formula and the SQL return the same 8 rows.

Where does a straight translation go wrong?

Common mistake

Why does to_days(CAST(x AS INTEGER)) move the time?

to_days takes whole days, and the cast rounds 1.5 to 2. Tableau adds 1.5 days, 36 hours. Add seconds instead.

Tableau

[d] + [x]

Tempting SQL

SELECT d + to_days(CAST (x AS INTEGER)) AS result
FROM input
ORDER BY id

SQL that matches

SELECT d + to_seconds(x * 86400) AS result
FROM input
ORDER BY id
xdTableauTempting SQLSQL that matches
1.52024-02-28 18:002024-03-01 06:002024-03-01 18:002024-03-01 06:00
−2.52024-02-29 00:002024-02-26 12:002024-02-27 00:002024-02-26 12:00
0.12024-03-01 09:302024-03-01 11:542024-03-01 09:302024-03-01 11:54
2.252025-01-01 12:002025-01-03 18:002025-01-03 12:002025-01-03 18:00
nullnullnull
42024-01-01 08:002024-01-05 08:002024-01-05 08:002024-01-05 08:00
−12024-12-31 23:002024-12-30 23:002024-12-30 23:002024-12-30 23:00
32024-06-15 10:002024-06-18 10:002024-06-18 10:002024-06-18 10:00

The SQL that matches returns the Tableau result on every row; the marked cells are where the tempting SQL differs.

How do I add a fixed number of days?

A constant works the same way; a literal interval reads best in SQL. Columns 1 to 8 are the sample rows, by id.

CaseTableau, then SQL (DuckDB)12345678
plus 7 days[d] + 7d + INTERVAL 7 DAY2024-03-06 18:002024-03-07 00:002024-03-08 09:302025-01-08 12:00null2024-01-08 08:002025-01-07 23:002024-06-22 10:00

On every row, each Tableau formula and its SQL return the same value.

How do I write it in Snowflake or BigQuery?

The first example, written for each warehouse from its documentation. These are not run here; DuckDB is.

Snowflake

SELECT DATEADD(day, b, d) AS result
FROM input
ORDER BY id

Snowflake documentation

BigQuery

SELECT DATETIME_ADD(d, INTERVAL b DAY) AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own + (date) formula return?

Edit the formula and run it on the 8 sample rows. Fields: [id], [a], [b], [x], [p], [q], [s], [t], [d], [e].

Runs in your browser on the xmetric engine. Nothing is uploaded.

Which functions go with it?

What do people ask about + (date)?

Can I add months this way?

No; + adds days. Use DATEADD: DATEADD('month', 1, [d]).

Does adding to a date keep it a date?

Adding whole days to a date gives a date; adding to a date-time keeps the time of day.

Where is this documented?