StartTime-EndTime - 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

Transact-SQL (2005)

StartTime-EndTime

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2009-12-11 : 04:53:26

Hello
This is probably a simple task to you people but I am struggling.

I have two fields called starttime and endtime. I am trying to get the difference in minutes so I can get a duration. I have looked around but cannot get any solutions to work.
Can anyone advise please?

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 04:55:34

select Datediff(mi,starttime ,endtime)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-11 : 04:58:14

quote:
Originally posted by chipembele

Hello
This is probably a simple task to you people but I am struggling.

I have two fields called starttime and endtime. I am trying to get the difference in minutes so I can get a duration. I have looked around but cannot get any solutions to work.
Can anyone advise please?

What is the datatype of the columns?

Madhivanan

Failing to plan is Planning to fail

chipembele
Posting Yak Master

106 Posts

Posted - 2009-12-11 : 05:19:00

The datatype of the columns is datetime

senthil, I tried something like that but my script didnt like the SELECT in the middle of the other columns.

This is what I have at present.

SELECT     
TOP (100) PERCENT 
teaching.dbo.Staff.StaffRefNo, 
teaching.dbo.Staff.FirstName, 
teaching.dbo.Staff.Surname, 
teaching.dbo.RegisterSessionLecturer.StartTime, teaching.dbo.RegisterSessionLecturer.EndTime, 
teaching.dbo.Staff.Title, 
teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date, teaching.dbo.RegisterSessionLecturer.LecturerSessionID, teaching.dbo.Register.RegisterNo, 
teaching.dbo.GetWeekNo(teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date) AS WeekNo, 
teaching.dbo.StaffContract.ContractHours, teaching.dbo.RegisterSession.LecturersNote, teaching.dbo.Staff.UserDefined2
                        
FROM         
teaching.dbo.Staff WITH (NOLOCK) INNER JOIN
teaching.dbo.RegisterSessionLecturer WITH (NOLOCK)INNER JOIN
teaching.dbo.RegisterSession WITH (NOLOCK) ON 
teaching.dbo.RegisterSessionLecturer.RegisterSessionID = teaching.dbo.RegisterSession.RegisterSessionID INNER JOIN
teaching.dbo.Register WITH (NOLOCK) ON teaching.dbo.RegisterSession.RegisterID = teaching.dbo.Register.RegisterID ON 
teaching.dbo.Staff.StaffID = teaching.dbo.RegisterSessionLecturer.StaffID INNER JOIN
teaching.dbo.StaffCollegeLevelMembership WITH (NOLOCK) ON 
teaching.dbo.Staff.StaffID = teaching.dbo.StaffCollegeLevelMembership.StaffID INNER JOIN
teaching.dbo.CollegeLevel ON teaching.dbo.StaffCollegeLevelMembership.SID = teaching.dbo.CollegeLevel.SID LEFT OUTER JOIN
teaching.dbo.StaffContract ON teaching.dbo.Staff.StaffID = teaching.dbo.StaffContract.StaffID

WHERE     (teaching.dbo.Register.AcademicYearID = '09/10') AND (teaching.dbo.CollegeLevel.Code <> 'COL')

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 05:26:24

Do you want to include in Where Clause or ON Clause??

Like this???

Select * from table_name where Datediff(mi,starttime ,endtime) >50

Where u want to exactly include Datediff???

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

chipembele
Posting Yak Master

106 Posts

Posted - 2009-12-11 : 05:30:53

In this part

SELECT     
TOP (100) PERCENT 
teaching.dbo.Staff.StaffRefNo, 
teaching.dbo.Staff.FirstName, 
teaching.dbo.Staff.Surname, 
teaching.dbo.RegisterSessionLecturer.StartTime, teaching.dbo.RegisterSessionLecturer.EndTime, 
teaching.dbo.Staff.Title, 
teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date, teaching.dbo.RegisterSessionLecturer.LecturerSessionID, teaching.dbo.Register.RegisterNo, 
teaching.dbo.GetWeekNo(teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date) AS WeekNo, 
teaching.dbo.StaffContract.ContractHours, teaching.dbo.RegisterSession.LecturersNote, teaching.dbo.Staff.UserDefined2, DATEDIFF

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 05:38:24

quote:

Originally posted by chipembele

In this part

SELECT     
TOP (100) PERCENT 
teaching.dbo.Staff.StaffRefNo, 
teaching.dbo.Staff.FirstName, 
teaching.dbo.Staff.Surname, 
teaching.dbo.RegisterSessionLecturer.StartTime, teaching.dbo.RegisterSessionLecturer.EndTime, 
teaching.dbo.Staff.Title, 
teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date, teaching.dbo.RegisterSessionLecturer.LecturerSessionID, teaching.dbo.Register.RegisterNo, 
teaching.dbo.GetWeekNo(teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date) AS WeekNo, 
teaching.dbo.StaffContract.ContractHours, teaching.dbo.RegisterSession.LecturersNote, teaching.dbo.Staff.UserDefined2, DATEDIFF

Whats the Problem include here??

Select .....,datediff(mi,teaching.dbo.RegisterSessionLecturer.StartTime,
teaching.dbo.RegisterSessionLecturer.EndTime)..

Whats your output?? Do you got any error message??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-11 : 06:13:24

And remember that DATEDIFF(Minute,x, y) can give you results you didn't expect.

Consider:


DECLARE @a DATETIME
DECLARE @b DATETIME

SET @a = '1900-01-01T00:59:59'
SET @b = '1900-01-01T01:00:00'

SELECT DATEDIFF(MINUTE, @a, @b)
SELECT DATEDIFF(SECOND, @a, @b)

The result of the DATEDIFF for second and Minute are the same (1).

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

chipembele
Posting Yak Master

106 Posts

Posted - 2009-12-11 : 06:20:16

quote:
Originally posted by senthil_nagore

quote:
Originally posted by chipembele

In this part
SELECT
TOP (100) PERCENT
teaching.dbo.Staff.StaffRefNo,
teaching.dbo.Staff.FirstName,
teaching.dbo.Staff.Surname,
teaching.dbo.RegisterSessionLecturer.StartTime, teaching.dbo.RegisterSessionLecturer.EndTime,
teaching.dbo.Staff.Title,
teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date, teaching.dbo.RegisterSessionLecturer.LecturerSessionID, teaching.dbo.Register.RegisterNo,
teaching.dbo.GetWeekNo(teaching.dbo.Register.AcademicYearID, teaching.dbo.RegisterSession.Date) AS WeekNo,
teaching.dbo.StaffContract.ContractHours, teaching.dbo.RegisterSession.LecturersNote, teaching.dbo.Staff.UserDefined2, DATEDIFF

Whats the Problem include here??

Select .....,datediff(mi,teaching.dbo.RegisterSessionLecturer.StartTime,
teaching.dbo.RegisterSessionLecturer.EndTime)..

Whats your output?? Do you got any error message??

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

It's working now. I'll have done something wrong. Thanks for your help.

Thanks to the other people too for your replies.

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-12-11 : 23:30:07

Welcome ;)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources