Calculating Duration from Starttime and Endtime

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 2000 Forums

Transact-SQL (2000)

Calculating Duration from Starttime and Endtime

Author

Topic

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-04 : 17:41:21

I have two columns in my database,

starttime and endtime which are varchar (not datetime)

and what I am trying to do is

Startime - endtime = duration

and I was wondering if there is an easy way to get the duration from those two fields or would be it be easier to convert them to datetime and do some calculations that way?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2010-10-04 : 18:00:29

Dates should be stored in Datetime or smallDatetime columns. Yes, it would be easier to convert them to datetime then apply the DATEDIFF function.

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-04 : 18:36:34

This will give you duration as an offset from 1900-01-01 00:00:00.000

After that, you can use the datediff and datepart functions to break it out into units of time.


select
	Duration =
	convert(datetime,'20101227 04:36:44:993') -
	convert(datetime,'20090624 02:45:33:447')

Result:


Duration 
------------------------
1901-07-06 01:51:11.547

More info about this on the link below uder "Uses of the DATETIME data type"
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-05 : 11:27:21

With that said, is it possible to convert 2 field into a date time field? I currently have the fields

exceptiondate - datetime
starttime - varchar
endtime - varchar

and what Id like to do is to convert/consolitdate the first two fields to just exceptionstart and have field 2 be exceptionend and both be datetime. How difficult would that be?

Thank you

Doug

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-05 : 11:47:50

It is possible to convert the varchar field to datetime field but we need to see some sample data.

dougancil
Posting Yak Master

217 Posts

Posted - 2010-10-05 : 16:11:51

PK,

Here are some lines of sample data:

8302 8/23/2007 9:00 AM 5:00 PM 8:00 Vacation Ryan 8/27/2007 1/1/1900 5:09:00 PM
8342 10/19/2007 1:45 PM 2:30 PM 0:45 Training Session Ryan 10/19/2007 1/1/1900 2:47:00 PM
8245 10/19/2007 2:00 PM 02:15pm 0:15 Approved Technical Reason Celia 10/19/2007 1/1/1900 4:28:00 PM
8345 10/19/2007 6:30 PM 8:30 PM 2:00 Training Session Ryan 10/19/2007 1/1/1900 8:39:00 PM

Fields 3 and 4 in this example, even though they show a time are varchar. Is this the kind of sample that you were looking for?

Thank you

Doug

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-10-05 : 16:22:45

Sample data means posting code that can be used to load a temp table for testing.

What you posted doesn't even make clear the columns boundries, column names, data types, or anything useful.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources