# How to get the difference in datetime of consecutive rows [Answered]RSS

## 7 replies

Last post Sep 13, 2018 10:34 AM by tvb2727

• tvb2727

Participant

1011 Points

2693 Posts

### How to get the difference in datetime of consecutive rows

How do I get the difference between each row in terms of datetime?

Say I have 5 rows:

EventDt

9/6/2018 13:00

9/6/2018 13:03

9/6/2018 13:09

So the results would be

0

3

6 for the difference.

• PatriceSc

All-Star

46080 Points

16784 Posts

### Re: How to get the difference in datetime of consecutive rows

Hi,

Starting with SQL Server 2012 you have https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017

```-- Just to simulate a table with actual data...
WITH Data AS
(
SELECT CAST('20180906 13:00' AS SMALLDATETIME) AS value
UNION ALL SELECT '20180906 13:03'
UNION ALL SELECT '20180906 13:09'
)
-- Actual suggestion starts here as if we had a "Data" table
SELECT value,DATEDIFF(minute,LAG(value) OVER(ORDER BY Value),value)
FROM Data```

• tvb2727

Participant

1011 Points

2693 Posts

### Re: How to get the difference in datetime of consecutive rows

#### PatriceSc

Hi,

Starting with SQL Server 2012 you have https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017

```-- Just to simulate a table with actual data...
WITH Data AS
(
SELECT CAST('20180906 13:00' AS SMALLDATETIME) AS value
UNION ALL SELECT '20180906 13:03'
UNION ALL SELECT '20180906 13:09'
)
-- Actual suggestion starts here as if we had a "Data" table
SELECT value,DATEDIFF(minute,LAG(value) OVER(ORDER BY Value),value)
FROM Data```

Thanks that helped.

I have this below, but how can I only pull greater than 2 minutes? or 120 seconds?

I tried to reference DifferenceValue in my WHERE clause, but it says it is an invalid column.

```  SELECT DateAdd(hour, -5, [EVENT_DT]) as central, DATEDIFF(SECOND ,LAG([EVENT_DT]) OVER(ORDER BY [EVENT_DT]),[EVENT_DT]) as DifferenceValue
FROM [UTL_EVENT_LOG]
order by EVENT_DT desc```

• PatriceSc

All-Star

46080 Points

16784 Posts

### Re: How to get the difference in datetime of consecutive rows

Something like

SELECT * FROM (
SELECT value,DATEDIFF(minute,LAG(value) OVER(ORDER BY Value),value) AS alias
FROM Data
) x
WHERE alias>3

works.

• Brando ZWZ

Star

9611 Points

3033 Posts

### Re: How to get the difference in datetime of consecutive rows

Sep 07, 2018 09:29 AM|Brando ZWZ|LINK

Hi tvb2727,

What do you mean about "pull greater than 2 minutes? or 120 seconds"?

Do you mean you want to return greater than 2 minutes's record?

Best Regards,

Brando

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• tvb2727

Participant

1011 Points

2693 Posts

### Re: How to get the difference in datetime of consecutive rows

#### Brando ZWZ

Hi tvb2727,

What do you mean about "pull greater than 2 minutes? or 120 seconds"?

Do you mean you want to return greater than 2 minutes's record?

Best Regards,

Brando

My task should be every 2 minutes, so I want to see if the scripts are running every 2 minutes.

• Brando ZWZ

Star

9611 Points

3033 Posts

### Re: How to get the difference in datetime of consecutive rows

Sep 10, 2018 07:39 AM|Brando ZWZ|LINK

Hi tvb2727,

According to your description, I suggest you could consider using the SQL Server Agent Schule job to achieve your requirement.

By using this , you could run the sql query every 2 seconds in the SSMS.

More details about how to use it, you could refer to below article:

https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-2017

https://stackoverflow.com/a/2348899

Best Regards,

Brando

MSDN Community Support
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
• tvb2727

Participant

1011 Points

2693 Posts

### Re: How to get the difference in datetime of consecutive rows

#### Brando ZWZ

Hi tvb2727,

According to your description, I suggest you could consider using the SQL Server Agent Schule job to achieve your requirement.

By using this , you could run the sql query every 2 seconds in the SSMS.

More details about how to use it, you could refer to below article:

https://docs.microsoft.com/en-us/sql/ssms/agent/schedule-a-job?view=sql-server-2017

https://stackoverflow.com/a/2348899

Best Regards,

Brando

This could help me with other task, but not this specific one! Thanks.