Help on time difference query - 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

General SQL Server Forums

New to SQL Server Programming

Help on time difference query

Author

Topic

yumyum113
Starting Member

31 Posts

Posted - 2010-05-11 : 11:06:33

Hi all,

I have two text field that stores starttime and endtime respectively.

I have this sample data below:

StartTime EndTime
9:00 am 10:00 am
10:45 am 11:40 am

My problem is how to get the time difference (in minutes or hours) of the two fields.

Hope someone could help me. As always thank you for taking time.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-05-11 : 12:03:58

Do you want to do that on DB or Application ?

yumyum113
Starting Member

31 Posts

Posted - 2010-05-11 : 12:08:31

On DB.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-11 : 13:18:57

Try this:

I am making assumption that you are using varchar datatype for storing the time field.
In case you are using sql 2008 then you can directly use datediff function

Declare @TestTable Table
(
StartTime varchar(10),
EndTime varchar(10))

Insert into @TestTable
Select '9:00 am','10:00 am' union
select '10:45 am', '11:40 am' union
select '10:55 am', '1:40 pm'

Select StartTime, EndTime,
Datediff(Mi,Convert(datetime, '1900-01-01 ' + StartTime ,120),Convert(datetime, '1900-01-01 ' + EndTime ,120)) as DiffInMin from @TestTable

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

yumyum113
Starting Member

31 Posts

Posted - 2010-05-11 : 13:26:32

Thanks bohra, it works!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources