SQL QUERY subtraction query

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-08-30
I need to preform subtraction on a table and display the difference.
I need to subtract 2 times

id        Date
1         2012-04-20 10:48:15.363
2         2012-04-20 10:48:15.383
3         2012-04-20 10:48:37.643
4         2012-04-20 10:48:37.793

I need to subtract id 2 Date - id 1 Date
which is .20
Question by:Leo Torres
LVL 15

Expert Comment

ID: 38350145
If you are just doing two specific IDs you could do something like this.

   CAST((SELECT date FROM YourTable WHERE ID = 1) - (SELECT Date FROM YourTable WHERE ID = 2) AS Time)
LVL 25

Accepted Solution

lwadwell earned 2000 total points
ID: 38350256
You could also do
SELECT a.id, DATEDIFF(ms, b.Date, a.Date)
FROM yourtable a
INNER JOIN yourtable b ON a.id + 1 = b.id

if only two rows

SELECT a.id, DATEDIFF(ms, b.Date, a.Date)
FROM yourtable a
INNER JOIN yourtable b ON a.id + 1 = b.id
WHERE a.id = 1

Author Closing Comment

by:Leo Torres
ID: 38350941
Thank you!

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question