time difference - business hours only

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)

time difference - business hours only

Author

Topic

himi
Starting Member

3 Posts

Posted - 2009-03-06 : 12:48:53

Hello all,
I have a simple database of tickets. Once ticket is submitted there is DATETIME saved. I need to count time of the ticket but during business hours only, let's say 9-17 and on week days only. Also clock can be stopped when ticket is in "pending" status.

I found another topic here on forums but it is not finished and did not work for me.

I can count time on week days only:
SELECT id FROM tickets WHERE DATE_FORMAT(submitted, '%W') NOT IN ('Saturday','Sunday')

..but those business hours are mystery for me.

Anyone have any ideas?
Many thanks in advance!!

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-06 : 13:06:11

How about ..

SELECT id FROM TICKETS WHERE (DATEPART(WEEKDAY,Submitted) Between 2 AND 6) AND (DATEPART(HOUR,Submitted) Between 9 AND 17)

?

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:33:01

are you using sql server?

himi
Starting Member

3 Posts

Posted - 2009-03-09 : 04:30:56

Hello, sorry for late reply. Thanks. I'm using MySQL and when I try what guptam suggested it says "FUNCTION DATEPART does not exist".

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-03-09 : 08:33:39

Please see the below article for the date and time functions.
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I think the equalent for
datepart(weekday,yourdate) is weekday(),dayofweek()
datepart(hour,yourdate) is hour().
so try to use them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-09 : 08:49:20

quote:
Originally posted by himi

Hello, sorry for late reply. Thanks. I'm using MySQL and when I try what guptam suggested it says "FUNCTION DATEPART does not exist".

Post your question at mysql forums such as www.mysql.com

Madhivanan

Failing to plan is Planning to fail

himi
Starting Member

3 Posts

Posted - 2009-03-09 : 10:55:22

Thanks & Sorry!

guptam
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 14:12:03

heh yaa my bad that was SQL Server version hehe .. Good luck finding answer :) I haven't touched MySQL for over 6 years can't help there heh.

--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources