Find the date difference between rows in Visual SQL
When wanting to calculate the difference in time between two date columns, the Date difference formula option in the Calculated Column or Edit Formula Actions can be used.
In the event you only have one date column but would like to calculate the difference between each row, you can instead use the datediff()
function in a Custom formula.
Using the lag() function
An easy way to do this is to use the lag() function—which we’ve conveniently turned into a Guided Formula you can use—in your Calculated Column or Edit Formula Action to create a start column to use as part of your date calculation.
In the example below, we’ll calculate the number of days between user login times.

First, query the column containing the login dates and run the query. For this example, we’ll use the Last Login column from the Users table of our SaaS Company Demo Data data source. We rename the column to Signed On.

Add a Calculated Column, which will act as the start date to calculate the date difference. We’ll then select the Lag Guided Formula and set a Row Offset of
1
. Click Save to save the Action. 
After creating the offset column, we’ll want to create another column to calculate the number of days between login dates. To do this, we add another Calculated Column and select the Date Difference Guided Formula. Then, select the start and end columns as well as the unit of time. For this example, the start date column is Lag, the end date column is Signed On, and the unit of time is
day
. Click Save to save the Action.
Instead of the Guided Formula option, you can also use the datediff()
builtin formula below as part of a Custom formula, replacing the Start Date Column
and End Date Column
with the names of your columns and unit
with the unit of time you’d like to calculate (the unit
argument is optional):
datediff("Start Date Column","End Date Column",'unit')
Note: If you choose not to specify a unit in the formula, it will default to calculate days.
Using the lead() function
Another option is to skip adding an additional lag column and use the lead() window function in place of the end column in your datediff()
formula. This function acts similarly to the lag()
function we used above and will populate with the end date automatically.
To do this, add a Calculated Column, choose Custom formula for the formula type, then use the formula below, replacing Date Column
in the formula with the name of your date column (the order by
clause is optional):
datediff("Date Column",(lead("Date Column", 1) over (order by "Date Column"(optional))))
Here, we simply wanted to keep the current order of date rows; therefore, we omitted the order by
clause from the formula as follows:
datediff("Signed On",(lead("Signed On", 1) over ()))
You now have the number of days between login dates and can perform additional calculations as needed.
Read more about datetime calculations you can do in Visual SQL.