calculating with date and time

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 with date and time

Author

Topic

alexia
Starting Member

2 Posts

Posted - 2004-06-01 : 05:51:32

Hi, Can anyone help me to make a sql query (for a php website) in which to calculate with date and time??
There is: Userid, Begintime, Endingtime and Date. I have to get the hours a month (how much the person has worked that month). So userid x has worked y hours in january... rather nasty... Any help is welcome!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 06:09:46

what format are you storing the times in?

sounds like something along the lines of:

select userid,sum(datediff(endingtime,begintime, hours)
from TB
where datepart(month,date) = "month you are looking for"
group by userid

of course, depending on the data type, you may need to something whereby you take everything down to minutes, then add up...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-06-01 : 06:23:32

An example (with some fixes from my quick post):


create table testdates
(userid	int,
stdt	datetime,
edt	datetime,
dt	datetime)
go

insert into testdates values (1,'2004-05-01 08:00:00.000','2004-05-01 17:00:00.000','2004-05-01 08:00:00.000')
insert into testdates values (1,'2004-05-02 08:00:00.000','2004-05-02 17:00:00.000','2004-05-01 08:00:00.000')
insert into testdates values (1,'2004-05-03 08:00:00.000','2004-05-03 17:00:00.000','2004-05-01 08:00:00.000')
insert into testdates values (1,'2004-04-04 08:00:00.000','2004-04-04 17:00:00.000','2004-04-01 08:00:00.000')
insert into testdates values (2,'2004-05-05 08:00:00.000','2004-05-05 17:00:00.000','2004-05-01 08:00:00.000')
insert into testdates values (3,'2004-04-06 08:00:00.000','2004-04-06 17:00:00.000','2004-04-01 08:00:00.000')
go

select userid,sum(datediff(hour,stdt,edt)) as month_hours
from testdates
where datepart(month,dt) = 5
group by userid

outputs:


userid      month_hours 
----------- ----------- 
1           27
2           9

(2 row(s) affected)

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-06-01 : 06:28:02

Double post:-

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35691

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources