Manipulating Employees Punchtimes

Question

Manipulating Employees Punchtimes

ninel

Ten Centuries

Points: 1087
More actions
February 27, 2007 at 2:31 pm

#115993

I have a table that keps tracks of employees punch ins and outs.
CREATE TABLE [EmployeePunch] (
[scalldate] [varchar] (20) NULL ,
[sEmployeeId] [varchar] (20) NULL ,
[TotalHr] [numeric] (10,4) NULL ,
[sLogin] [varchar] (20) NULL ,
[sLogout] [varchar] (20) NULL
)
INSERT [EmployeePunch] (scalldate, sEmployeeId, TotalHr, sLogin)
VALUES ('20070227', '123', 8.5, '09:00:00')
INSERT [EmployeePunch] (scalldate, sEmployeeId, TotalHr, sLogin)
VALUES ('20070227', '456', 6.256, '09:00:00')
)
I have a file that needs to be loaded into that table.
The file contains sCalldate, sEmployeeId, totalhr, and slogin.
Ex:
20070227, 123, 8.5, 09:00:00
Does anyone know how to add 8.5 to 09:00:00 to get 17:30:00???

Viewing 3 posts - 1 through 2 (of 2 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you can, redesign your data storage... you should use a date time filed to store the date and the time together. if you need them separate, you can always pull them out formatted, but they should not be varchar columns. something like at the bottom this would be better:

my firststep was to merge your data back into a datetime field.

select convert(datetime, scalldate + ' ' + slogin) as punchtime,* from EmployeePunch

select dateadd(hour,8.5,convert(datetime, scalldate + ' ' + slogin)) as punchtime,* from EmployeePunch

select datediff(minute,convert(datetime, scalldate + ' ' + slogin),convert(datetime,sLogout) )

from EmployeePunch

my suggested redesign:

CREATE TABLE [EmployeePunch2] (

[sEmployeeId] [varchar] (20) NULL ,

[sLogin] datetime NULL ,

[sLogout] datetime NULL,

[TotalHr] AS datediff(minute,sLogin, sLogout) / 60.00

)

INSERT [EmployeePunch2] ( sEmployeeId, sLogin, sLogout)

VALUES ('123', '02/27/2007 09:00:00','02/27/2007 17:30:00')

INSERT [EmployeePunch2] ( sEmployeeId, sLogin, sLogout)

VALUES ( '456' ,'02/27/2007 09:00:00','02/27/2007 13:25:35')

select * from EmployeePunch2

results:

sEmployeeId	sLogin	sLogout	TotalHr
123	2007-02-27 09:00:00.000	2007-02-27 17:30:00.000	8.500000
456	2007-02-27 09:00:00.000	2007-02-27 13:25:35.000	4.416666

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

AMIT GUPTA-263376 SSCarpal Tunnel Points: 4244 More actions · Answer 2

Hi ,

Try This one!!!!

select convert(varchar,dateadd(mi,(8.5 * 60), convert(datetime,'09:00:00',108)),108)

Regrads ,

Amit G.