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

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 manufacturer found out.

As I see I need to first combine the dates and times into one cell as a datetime and then subtract the previous 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 开发者_Python百科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 an SQL Server server-side solution.

---

In Microsoft 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

---

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.

---

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.