updating time field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

updating time field

Author

Topic

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-20 : 08:58:06

i have a table emp

empid empin empout

1 08:00:00.000000 18:00:00.0000000

I want to update empin and empout coloumn so that it adds 15 min to current time

for example if empin is 08:00:00 should change to 08:15:00 and 18:00:00 to 18:15:00

how we will write a query that will update all the timein and timeout coloun to +15 min

thanx in advance

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-12-20 : 09:10:00

Use the DATEADD function

Jim

Everyday I learn something that somebody else already knew

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 10:57:12

JimF is correct DATEADD is what you need

You will probably want to do that with an After Insert trigger.

Here is an example query with the use of dateadd.

Create Table #TempEmployee
(
	empID int,
	empIN datetime,
	empOUT datetime
)
;

Insert into #TempEmployee Values(1,'2010-12-20 08:00','2010-12-20 16:00');
Insert into #TempEmployee Values(2,'2010-12-20 08:00','2010-12-20 16:00');
Insert into #TempEmployee Values(3,'2010-12-20 08:00','2010-12-20 16:00');
Insert into #TempEmployee Values(4,'2010-12-20 08:00','2010-12-20 16:00');
Insert into #TempEmployee Values(5,'2010-12-20 08:00','2010-12-20 16:00');
Insert into #TempEmployee Values(6,'2010-12-20 08:00','2010-12-20 16:00');

Update #TempEmployee
	Set empIN = (Dateadd(mi,15,empIN)),
	    empOUT = (Dateadd(mi,15,empOUT))
        Where empID = 1

Select *
From #TempEmployee;


Drop Table #TempEmployee;

empID       empIN                   empOUT
----------- ----------------------- -----------------------
1           2010-12-20 08:15:00.000 2010-12-20 16:15:00.000
2           2010-12-20 08:00:00.000 2010-12-20 16:00:00.000
3           2010-12-20 08:00:00.000 2010-12-20 16:00:00.000
4           2010-12-20 08:00:00.000 2010-12-20 16:00:00.000
5           2010-12-20 08:00:00.000 2010-12-20 16:00:00.000
6           2010-12-20 08:00:00.000 2010-12-20 16:00:00.000

Thank You,

John

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 11:12:10

The trigger should be something like this. (untested)

Create TRIGGER trgUpdate_emp
   ON  emp
   AFTER Insert
AS 
BEGIN
	SET NOCOUNT ON;

	Update emp
	    Set empIN = (Dateadd(mi,15,empIN)),
		empOUT = (Dateadd(mi,15,empOUT))
	From emp a
	    Inner Join Inserted As i
            On a.empID = i.empID
END

Thank You,

John

pnasz
Posting Yak Master

101 Posts

Posted - 2010-12-20 : 14:19:18

if i want to update the table where there are more than 100 emp.

SparkByte
Yak Posting Veteran

60 Posts

Posted - 2010-12-20 : 17:23:03

As in the 1st example, Remove the Where clause from the update and it will update all rows.

Be carefull and make a backup of your data before attempting on LIVE data.

Thank You,

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources