July 15, 2009 at 1:42 am
Hi all,
1. I'm working on an application which captures the attendance detail of employee in a Table, I need to find the difference of the sign_in and the sign_out time (both the column are datetime format)
The query used is given below
select
datediff(ss,signintime,signouttime) / 3600 as Hours,
(datediff(ss,signintime,signintime) % 3600) / 60 as Minutes
from Atttable
where AttendanceDate = 'Date'
the hours & minutes should be concatenated and stored in WorkedHours column. How Do I fetch the data in the format required (hh.mm)
July 15, 2009 at 5:33 am
HI,
I am not entirely sure what you want unless you can provide some test data and a script to create the table however if you are just looking to concatenate those fields together and display them in a column then something like this will probably do the job :
SELECT
CONVERT(VARCHAR(100),datediff(ss,[signintime],[signouttime]) / 3600 ) + ' : ' +
CONVERT(VARCHAR(100),datediff(ss,[signintime],[signintime]) % 3600 / 60) as HoursMinutes
from ms.dbo.account
where AttendanceDate = 'Date'
Let me know if this was what you were after.
July 15, 2009 at 5:36 am
I have just noticed that i have left the table in there that i was running this against. You will need to change the table back to the one that you are using.
Thanks
July 15, 2009 at 6:12 am
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 REPLACE(CONVERT(CHAR(5), @Span, 8), ':', '.')
-- Rounded hours and minutes (add 30 seconds)
SET @Span = @Out - @In + 0.000347222
PRINT REPLACE(CONVERT(CHAR(5), @Span, 8), ':', '.')
Results:
08.11
08.12
July 15, 2009 at 6:30 am
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 WorkedHours column needs to be calculated, sample data is as follows
EmployeeNo AttendanceDate Sign_In Sign_Out
12102009-04-03 00:00:00.0002009-04-03 08:35:00.0002009-04-03 20:18:00.000
12102009-05-03 00:00:00.0002009-05-03 08:00:00.0002009-06-03 05:18:00.000
12102009-06-03 00:00:00.0002009-06-03 14:15:00.0002009-06-03 22:10:00.000
The WorkedHours column need to be calculated as the difference between Signout - SignIn Time.
Note : The WorkedHours column is of type Decimal
July 15, 2009 at 7:21 am
Try this...
DECLARE @Sign_In datetime
DECLARE @Sign_Out datetime
DECLARE @WorkedHours DECIMAL(18,2)
DECLARE @hours varchar(30)
DECLARE @minutes varchar(2)
DECLARE @seconds varchar(30)
set @Sign_In = '2009-04-03 08:35:00.000'
set @Sign_Out = '2009-04-03 20:18:00.000'
set @seconds = abs(datediff(second, @Sign_In, @Sign_Out))
set @hours = @seconds / 3600
set @minutes = (@seconds - (@hours * 3600)) / 60
set @seconds = (@seconds - (@hours * 3600) - (@minutes * 60))
set @WorkedHours = @hours + '.' + @minutes
SELECT @WorkedHours AS WorkedHours
Thanks
Matt
July 15, 2009 at 7:39 am
I had assumed you wanted a formatted string, if you just want the decimal value then simply multiply the date difference by 24 (CAST to FLOAT required to allow multiplication). You can make WorkedHours a computed column.
CREATE TABLE dbo.Attendance_Summary(
EmployeeNo int NULL,
AttendanceDate datetime NULL,
Sign_In datetime NULL,
Sign_Out datetime NULL,
WorkedHours AS CAST(CAST(Sign_Out - Sign_In AS FLOAT) * 24 AS dec(10,2))
) ON PRIMARY
GO
INSERT INTO Attendance_Summary (EmployeeNo, AttendanceDate, Sign_In, Sign_Out)
SELECT 1210, '2009-04-03 00:00:00.000', '2009-04-03 08:35:00.000', '2009-04-03 20:18:00.000'
UNION ALL SELECT 1210, '2009-05-03 00:00:00.000', '2009-05-03 08:00:00.000', '2009-05-03 17:18:00.000'
UNION ALL SELECT 1210, '2009-06-03 00:00:00.000', '2009-06-03 14:15:00.000', '2009-06-03 22:10:00.000'
SELECT *
FROM dbo.Attendance_Summary
DROP TABLE dbo.Attendance_Summary
[font="Courier New"]EmployeeNo AttendanceDate Sign_In Sign_Out WorkedHours
----------- ---------------- ----------------- ----------------- -----------
1210 2009-04-03 2009-04-03 08:35 2009-04-03 20:18 11.72
1210 2009-05-03 2009-05-03 08:00 2009-05-03 17:18 9.30
1210 2009-06-03 2009-06-03 14:15 2009-06-03 22:10 7.92[/font]
I don't know what kind of robots you employ that can work a solid month-long shift, but I altered the second row of your sample data to fit human limitations.
July 15, 2009 at 8:09 am
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 and give feedback.
July 15, 2009 at 8:21 am
Scott Coleman (7/15/2009)
DECLARE @In DATETIME, @Out DATETIME, @Span DATETIMESET @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 REPLACE(CONVERT(CHAR(5), @Span, 8), ':', '.')
-- Rounded hours and minutes (add 30 seconds)
SET @Span = @Out - @In + 0.000347222
PRINT REPLACE(CONVERT(CHAR(5), @Span, 8), ':', '.')
Results:
08.11
08.12
True SSCrazy, the problem is the datatype is char and not decimal.
July 15, 2009 at 8:25 am
8:35AM to 8:18PM is over 12 hours? Is there a timezone difference here I'm not aware of?
The time difference is 11 hours 43 minutes. 11 + 43/60 = 11.72. I have no idea where you're getting 12.12 from.
July 15, 2009 at 8:33 am
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.
July 15, 2009 at 8:39 am
True SSCrazy, the problem is the datatype is char and not decimal.
In your original post the words "concatenate" and "format" implied you were looking for a character result. You didn't mention dec(10,2) until later.
the hours & minutes should be concatenated and stored in WorkedHours column. How Do I fetch the data in the format required (hh.mm)
You'll get the answer you're looking for more quickly if your question includes all required info.
Another forum tip is that SSCrazy is a designation for people with a certain number of posts. My name appears above "SSCrazy" just as yours appears above "SSC Rookie". I'm not offended, no need to apologize. Hopefully you'll be around here long enough to get called SSCrazy yourself.
July 15, 2009 at 8:44 am
Can someone validate and tell me whether I'm wrong
You need to be specific about your desired result. Are you looking for a simple decimal value with a unit of hours, or a composite decimal value whose whole part represents hours and whose fractional part represents minutes / 100?
July 15, 2009 at 9:24 am
Thanks Scott 🙂
a composite decimal value whose whole part represents hours and whose fractional part represents minutes / 100?
This is what i'm looking for...my query which I posted works fine, on condition when the SignIn & Signout are on the same day.
But changed the query...
update Attendance
set worked_hours =
rtrim(convert(char(5),datediff(ss,sign_in,sign_out) / 3600 )) + '.' +
rtrim(convert(char(5),(datediff(ss,sign_in,sign_out) % 3600) / 60))
July 15, 2009 at 11:20 am
Sign_in and Sign_out don't have to be on the same day, but the time span has to be less than 24 hours for the expression I used. Your latest expression handles shifts of up to 99999.99 hours. I'm glad I don't work there.
I don't like using character expressions for this kind of thing, even if they look simpler. Numeric expressions may look more complicated, but the server can do an incredible amount of math in the time it takes to allocate and deallocate string objects.
Here are some test cases with your expression and two variations of mine. You have to be careful with time conversions, and make sure you know how rounding and truncation is being done. Test your expression with all the cases you can think of. You don't seem to have tested for cases with 1-9 minutes, your expression turns them into 10-90 minutes.
You haven't said whether you want rounding or truncation, your expression truncates, but whichever you choose make sure it is done consistently. If you remove a single 3 from "0.0083333333333333" in my second expression it will round hours and minutes differently, changing "1.00" to "0.00".
SELECT Sign_In, Sign_Out, Sign_Out - Sign_In AS TimeSpan,
YourWorkHours = CAST(RTRIM(CONVERT(CHAR(5),DATEDIFF(ss,sign_in,sign_out) / 3600 )) + '.' +
RTRIM(CONVERT(CHAR(5),(DATEDIFF(ss,sign_in,sign_out) % 3600) / 60)) AS dec(10,2)),
MyWorkHours1 = CAST(FLOOR((CAST(Sign_Out - Sign_In AS FLOAT) * 86400 + 30) / 3600)
+ (CAST(CAST(Sign_Out - Sign_In AS FLOAT) * 86400 + 30 AS INT) % 3600 / 60) / 100.0 AS dec(10,2)),
MyWorkHours2 = CAST(FLOOR(CAST(Sign_Out - Sign_In AS FLOAT) * 24 + 0.0083333333333333)
+ (CAST(CAST(Sign_Out - Sign_In AS FLOAT) * 1440 + 0.5 AS INT) % 60) / 100.0 AS dec(10,2))
FROM (
SELECT CAST('2009-04-03 08:35' AS DATETIME) AS Sign_In, CAST('2009-04-03 20:18' AS DATETIME) AS Sign_Out
UNION ALL SELECT '2009-05-03 08:00', '2009-05-03 17:09'
UNION ALL SELECT '2009-06-03 14:15', '2009-06-03 22:10'
UNION ALL SELECT '2009-04-03 08:35', '2009-05-03 05:38'
UNION ALL SELECT '2009-05-10 07:59', '2009-05-23 04:47'
UNION ALL SELECT '2009-06-15 08:00:00', '2009-06-15 08:59:29.997'
UNION ALL SELECT '2009-06-16 08:00:00', '2009-06-16 08:59:30'
UNION ALL SELECT '2009-06-17 08:00:00', '2009-06-18 07:59:30') x[font="Courier New"]
Sign_In Sign_Out TimeSpan YourWorkHours MyWorkHours1 MyWorkHours2
----------------------- ----------------------- ----------------------- -------------- ------------- ------------
2009-04-03 08:35:00.000 2009-04-03 20:18:00.000 1900-01-01 11:43:00.000 11.43 11.43 11.43
2009-05-03 08:00:00.000 2009-05-03 17:09:00.000 1900-01-01 09:09:00.000 9.90 9.09 9.09
2009-06-03 14:15:00.000 2009-06-03 22:10:00.000 1900-01-01 07:55:00.000 7.55 7.55 7.55
2009-04-03 08:35:00.000 2009-05-03 05:38:00.000 1900-01-30 21:03:00.000 717.30 717.03 717.03
2009-05-10 07:59:00.000 2009-05-23 04:47:00.000 1900-01-13 20:48:00.000 308.48 308.48 308.48
2009-06-15 08:00:00.000 2009-06-15 08:59:29.997 1900-01-01 00:59:29.997 0.59 0.59 0.59
2009-06-16 08:00:00.000 2009-06-16 08:59:30.000 1900-01-01 00:59:30.000 0.59 1.00 1.00
2009-06-17 08:00:00.000 2009-06-18 07:59:30.000 1900-01-01 23:59:30.000 23.59 24.00 24.00[/font]
If you insist on using character expressions, you could fix yours by replacing the second half with
REPLACE(STR((DATEDIFF(ss,sign_in,sign_out) % 3600) / 60, 2, 0), ' ', '0')
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply