Subtracting two columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Subtracting two columns

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-02 : 15:44:33

Hello Everyone:

I have two columns one with a start time and another with a stop time. In this way are the columns filled:
StartTime Stop Time
9/13/2005 9:31:48 PM 9/14/2005 7:02:06 AM
9/13/2005 9:07:25 PM 9/14/2005 7:21:21 AM

Is there any way to subtract StopTime - StartTime and have results in a new column called duration?

Thanks in Advance!
Dasman

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-02 : 17:06:23

stoptime-starttime will do it assuming they are datetimes - otherwise convert first.
You can convert to varchar to format the value.
Probably no need to create a new column - just calculate when you need it.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-07 : 13:48:16

Hey Nigel and others reading,

I tried this:
SELECT * From table_name
StopTime - StartTime

It gave this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '-'.

How should the code look?

Thanks in Advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 14:51:12

It should be more like,

SELECT *,StopTime - StartTime From table_name

But that may give you a date sometime around the turn of the century in the year 1900. You can extract the hour, minutes etc. from that date which would correspond to the duration.

The issue here is that there is no "TimeSpan" data type or structure in SQL in the same way as there is in .Net. So you have to improvise. For example, you could do the following:


SELECT 
   datediff(s,StartTime,StopTime) AS DurationInSeconds
FROM
   YourTable;

But that gives you the result in seconds. If you want it in hours, minutes etc., you could do this:


SELECT
   datediff(hh,StartTime,StopTime)/24 AS days,
   datediff(hh,StartTime,StopTime)%24 hours,
   datediff(mi,StartTime,StopTime)%60 minutes,
   datediff(s,StartTime,StopTime)%60 seconds
FROM
   YourTable

There are other ways, but each with a little baggage of its own.

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-07 : 15:16:55

Hello Sunita and others reading,

This seems to work:
SELECT datediff(s,StartTime, StopTime) AS Duration, Table_Name.Column2
FROM Table

This creates a Duration column with events from 0 to 30 seconds. say I only want the values with duration of more than 0 seconds. I can't use this Where clause

Where Duration > 0

Gives an error. Is there a way to establish this as a column?

Thanks,
Shijit

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-07 : 15:39:17

The alias (Duration) is not available in the where clause, so you have to repeat the datediff expression you used in the select clause as in:


SELECT datediff(s,StartTime, StopTime) AS Duration, Table_Name.Column2
FROM Table
WHERE datediff(s,StartTime, StopTime) > 0

However, for duration to be less than zero, StopTime will have to be earlier than StartTime. Is that possible according to your business rules?

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-06-07 : 16:00:01

The duration is never less than 0. Its always either 0 or more. Thus a simpl >0 gives me the data I need.

and Im in a research lab!. Using excel to analyze data. Thanks the above code u posted should work!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-07 : 16:27:22

quote:
The duration is never less than 0. Its always either 0 or more

Since you are calculating duration from 2 other columns, you cannot guarantee that unless you have a CHECK constraint on your table that forces StopTime to be greater than StartTime. Never assume your data is correct; apply constraints to guarantee it.

This query may perform better if StartTime and/or StopTime are indexed:

SELECT datediff(s,StartTime, StopTime) AS Duration, Table_Name.Column2
FROM Table
WHERE StopTime > StartTime

However, that will pick up rows where Start and Stop time differ by less than 1 second, so it's not exactly the same query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources