Thanks a ton Scott...

Missed pasting the complete code...

Below is the complete code

update Attendance

set workedhours =

--convert(decimal(10,2),REPLACE(CONVERT(char(5),es_sign_out - es_sign_in,8),':','.'))

convert(decimal(10,2),(rtrim(convert(char(5),datediff(ss,sign_in,sign_out) / 3600 )) + '.' +

rtrim(convert(char(5),(datediff(ss,sign_in,sign_out) % 3600) / 60)) ))

The answer...

Thanks Scott 🙂

This is what i'm looking for...my query which I posted...

Tried this query

--begin tran

--update Attendance

--set workedhours = convert(decimal(10,2),REPLACE(CONVERT(char(5),sign_out - sign_in,8),':','.'))

--where attendancedate = '04/03/09'

--commit tran

Can someone validate and tell me whether I'm wrong, SSHR took a leaf from your query.

Scott Coleman (7/15/2009)

---

DECLARE @In DATETIME, @Out DATETIME, @Span DATETIME

SET @In = '2009-07-13 7:53:00 AM'

SET @Out = '2009-07-13 4:04:45 PM'

-- Full hours and minutes (no rounding)

SET @Span = @Out - @In

PRINT...

Hi SSCrazy,

select

CAST(CAST(sign_out - sign_in AS FLOAT) * 24 AS dec(10,2)) wrkdhrs

from Attendance

where es_attendancedate = '04/03/09'

gives an o/p at 11.72 which should be 12.12 hrs.

Wud try implementing the other choices...

To make things for better understanding, find below the table structure which I have created....

CREATE TABLE [dbo].[Attendance_Summary](

[EmployeeNo] [int] NULL,

[AttendanceDate] [datetime] NULL,

[Sign_In] [datetime] NULL,

[Sign_Out] [datetime] NULL,

[WorkedHours] [decimal](10, 2) NULL,

) ON [PRIMARY]

The...

Thanks Alan! The reason for multiple ldf's was done by previous DBA, who created multiple ldf when complained of log getting filled.

Atleast to make life easier henceforth, thought to set...

Thanks Ganesh & Sergiy,  thought of a workaround, so that i passed the date too as a parameter, but until today never knew that UDF cannot house getdate().

One thing learnt...

I was using the begin tran for a select statement rather than the update statement, but fought tough to spot it out.  Modified...and used it at the right portion and...

oh buddy Bob, forgot to tell u that tried that first, still getting the same error message...

Arun

Gila,

We are capturing day by day hours and not the total hours hence is the columns repeating for everyday...do let me know if i'm right.

Arun

and u r right graham, i missed out the rs.movenext.  I have done that and it worked.

Thanks again.

 

Hit on the bull's eye, that is what is wanted...

Chris, just started working on sql and when would i provide solution for others like this.

3 Cheers

Arun

That worked, but didn't work for the pubs.authors and it worked when i created a table of my own (testing with 2 fields).

Thanks all hope i stop my doubts for 2day...catch...

Thanks a ton Ray, the first one worked like a bub.

But i faltered in trying the second option...created the table sales_copy (copy of sales in pubs database)

here is the sql...