-2

I found this answer regarding the possibility to get the difference between two rows for a column field ( https://stackoverflow.com/questions/634568/how-to-get-difference-between-two-rows-for-a-column-field#= )

My question is, how can I make the same thing, when the Value is a Timestamp? I am using SQL Server 2012.

The table looks like in the picture below

Basically, what I want to do is to get the difference between two consecutive timestamps to see after how much time, a new DMC went through the process

Hope it's more clear right now. I'm pretty new at this.

Thank you in advance.

Community
  • 1
  • 1
Hory Hene
  • 31
  • 6

1 Answers1

0

Since you are on SQL Server 2012, you can now use LAG/LEAD to get a value of a column from the previous/next row. Then you just calculate the difference as you would between two columns on the same row. Something like this:

create table YOUR_TABLE(ID integer, DT datetime);
insert into YOUR_TABLE
    select 2, '02/02/2016 12:00:00' union all
    select 3, '02/05/2016 12:00:00' union all
    select 4, '02/06/2016 12:00:00' union all
    select 5, '02/07/2016 12:00:00' 
;

select 
    ID,
    DT,
    datediff(day, DT, lead(DT) over (order by ID)) as DIFF
from your_table;
msheikh25
  • 576
  • 3
  • 9
  • datetime <> timestamp – Lukasz Szozda May 25 '16 at 18:55
  • I expect it will work, my problem is that the Full_time_stamp column has the datatype varchar (It was imported automatically from an .csv file. With convert(datetime,..) it didn't work to convert. Is there any other way to convert it? Or should I import the data once again and try to set the datatype of the column since the import? Thank you – Hory Hene May 25 '16 at 19:07
  • It would probably be ideal if you get the data in the right format in your table first while importing. But if it is currently a varchar in your table, you can substring and then cast it as datetime. Try this: `select cast(substring(DT,1,19) as datetime) from your_table` – msheikh25 May 25 '16 at 19:38