In sql I would like to subtract a date from a previous row date

Question

The problem is that the dates are stored in the SQL database as nvarchar() and the times are stored in a separate column. I only have read access to this database so I can't change the format. Besides, it would probably void our support if the manufacture found out.

As I see I need to first combine the dates and times into one cell as a datetime and then subtract prev row from the current.

ActualTime, ActualDate
5:30:26,    31-Dec-09
16:01:47,   31-Dec-09
17:35:50,   31-Dec-09
18:31:31,   31-Dec-09
18:51:03,   31-Dec-09
18:55:35,   31-Dec-09
19:26:53,   31-Dec-09
 5:25:37,   1-Jan-10
5:38:36,    1-Jan-10
5:46:58,    1-Jan-10
6:27:00,    1-Jan-10

Several people have asked what language I was using. I was hoping to do all of this at the server. On the code side (C#) it's a trivial problem. Like I said I am looking for a SQL server side solution

Answer 1

What language are you using, and what kind of Database is it? I'm not sure if the database has capabilities to do row manipulation within a query (subtracting one row from the other), so you would have to do this programmatically. I'm not sure what language you're using, but if it has a Date or Time API then you can use that to create a Date object. There should a function that returns the total number of seconds since a starting date (January 1, 1970 or something). You create your two Date objects, convert into number of seconds and then subtract them. You can then calculate the number of days between them.

Answer 2

If you're using PHP, I suggest you use the strtotime() function to convert it into a time object. Do this for both dates. The difference will give you the number of seconds between them.

Answer 3

In MS SQL server

to convert your columns in a date you can

Select Cast( ActualDate + ' ' + ActualTime AS DateTime)

to compare between two dates

Select 
  Datediff(
           second,
           Cast('13-dec-2009 ' + '19:39:33' As DateTime),
           Cast('13-dec-2009 ' + '19:26:33' As DateTime)
          )

More on DATEDIFF (Transact-SQL) parameters

And to get the difference from the current date/time use the GETDATE()

    Select 
       *, 
       oldness = DateDiff(
                   second,
                   GETDATE(),
                   Cast(ActualDate + ' ' + ActualTime AS DateTime)
                  )
    From
       your_table

Finally to do it between rows (for the whole table..)

Select  *,
        Cast(ActualDate + ' ' + ActualTime AS DateTime) as [fulldate],
        DiffFromPrevious = Coalesce(
           DateDiff(
                        second, 
                        (
                          Select Top 1 Cast(ActualDate + ' ' + ActualTime AS DateTime) AS [fulldate] 
                          From yourtable 
                          Where Cast(ActualDate + ' ' + ActualTime AS DateTime) < Cast(t1.ActualDate + ' ' + t1.ActualTime AS DateTime) 
                          Order By [fulldate] Desc
                        ),
                        Cast(ActualDate + ' ' + ActualTime AS DateTime)
                       ),
        0) 
From 
  yourtable t1
Order By 
  [fulldate] Asc