February 27, 2007 at 2:31 pm
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???
February 27, 2007 at 2:54 pm
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
February 28, 2007 at 3:30 am
Hi ,
Try This one!!!!
select convert(varchar,dateadd(mi,(8.5 * 60), convert(datetime,'09:00:00',108)),108)
Regrads ,
Amit G.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply