Working with time part of datetime

Question

Working with time part of datetime

kpspencer

Mr or Mrs. 500

Points: 586
More actions
December 10, 2003 at 5:52 am

#83680

I have a column that stores hours worked by eng. the data is stored as datetime
EngJobTime
CDP 1900-01-01 01:00:00.000
CDP 1900-01-01 02:45:00.000
CDP 1900-01-01 01:40:00.000
CDP 1899-12-31 23:00:00.000
the time portion represents the number of hours worked on a job.
I want to be able to strip out the time portion and then sum up the hours work. for the above example the answer I need is 28.25 hours.
Can anyone help me
Thanks in advance
Karl

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ThomasH SSCertifiable Points: 7437 More actions · Answer 1

Here is a little example that might help...



-- Show sample time data 

Select Time,  

DateDiff(HH, '1/1/00', Time) % 24 as TheHours,  

DateDiff(n, '1/1/00', Time) % 60 as TheMinutes 

From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A 

 

-- Calc Hours & Minutes 

Select Sum(DateDiff(HH, '1/1/00', Time) % 24) as TheHours, 

Sum(DateDiff(n, '1/1/00', Time) % 60) as TheMinutes 

From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A 



-- Put it together 

Select Sum(DateDiff(HH, '1/1/00', Time) % 24) + Convert(Int, Sum(DateDiff(n, '1/1/00', Time) % 60) / 60) as TheHours, 

Sum(DateDiff(n, '1/1/00', Time) % 60) % 60 as TheMinutes 

From (select top 4 CrDate as Time From SysObjects order by CrDate desc) A

Once you understand the BITs, all the pieces come together

kpspencer Mr or Mrs. 500 Points: 586 More actions · Answer 2

kpspencer

Mr or Mrs. 500

Points: 586

December 10, 2003 at 7:15 am

#485531

Thanks. That appears to do the trick

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

FLOOR(SUM(DATEPART(hh,TimeCol))+SUM(DATEPART(n,TimeCol))/60)+(SUM(DATEPART(n,TimeCol))%60)/100.

--Jonathan

r_achar SSC-Addicted Points: 432 More actions · Answer 4

Hi Karl,

I am glad you could solve the problem. Here is some thought:

Perhaps it is not the best way to store the number of hours. You will be better off by storing the hours in a separate column. This will make the system simpler to maintain.

What say you?

RA

r_achar SSC-Addicted Points: 432 More actions · Answer 5

Hi,

Here is an addendum:

In my previous post, I meant storing hours in a separate column with a different data type (float or real or whatever) - not as datetime.

Thanks

RA