xmetricFunctions

Tableau function Operators

Tableau - (date, days) to SQL

[d] - [b] moves a date-time back by b days and keeps its time of day. In DuckDB, subtract an interval: d - to_days(b). Casting to DATE first, so that plain integer subtraction works, throws the time away.

Updated · Checked on the xmetric engine

What is the syntax?

date - number
date
a date or date-time.
number
days to subtract.

Returns a date or date-time number days earlier. 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 before d?

Tableau

[d] - [b]

SQL (DuckDB)

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

Row 1: 28 February 18:00 minus 2 days is 26 February 18:00.

bdResult
22024-02-28 18:002024-02-26 18:00
22024-02-29 00:002024-02-27 00:00
52024-03-01 09:302024-02-25 09:30
42025-01-01 12:002024-12-28 12:00
3null
22024-01-01 08:002023-12-30 08:00
2024-12-31 23:00null
−22024-06-15 10:002024-06-17 10:00

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

Where does a straight translation go wrong?

Common mistake

Why does casting to DATE lose the time?

DuckDB lets you subtract an integer from a DATE, but the cast drops the time of day. Tableau keeps it: 18:00 stays 18:00.

Tableau

[d] - [b]

Tempting SQL

SELECT CAST (d AS DATE) - CAST (b AS INTEGER) AS result
FROM input
ORDER BY id

SQL that matches

SELECT d - to_days(b) AS result
FROM input
ORDER BY id
bdTableauTempting SQLSQL that matches
22024-02-28 18:002024-02-26 18:002024-02-262024-02-26 18:00
22024-02-29 00:002024-02-27 00:002024-02-272024-02-27 00:00
52024-03-01 09:302024-02-25 09:302024-02-252024-02-25 09:30
42025-01-01 12:002024-12-28 12:002024-12-282024-12-28 12:00
3nullnullnull
22024-01-01 08:002023-12-30 08:002023-12-302023-12-30 08:00
2024-12-31 23:00nullnullnull
−22024-06-15 10:002024-06-17 10:002024-06-172024-06-17 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 go back one day?

Subtract a constant; in SQL, a literal interval. Columns 1 to 8 are the sample rows, by id.

CaseTableau, then SQL (DuckDB)12345678
minus 1 day[d] - 1d - INTERVAL 1 DAY2024-02-27 18:002024-02-28 00:002024-02-29 09:302024-12-31 12:00null2023-12-31 08:002024-12-30 23:002024-06-14 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_SUB(d, INTERVAL b DAY) AS result
FROM input
ORDER BY id

BigQuery documentation

What does your own - (date, days) 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, days)?

What does a negative number do?

It moves forward: row 8 has b = -2, so the result is two days later.

How do I subtract months?

Use DATEADD with a negative number: DATEADD('month', -1, [d]).

Where is this documented?