CODEWITHSUNDEEP
sqlsql-server-2008datetime
user2612969
user2612969

Reputation: 67

Update time only in a datetime column in SQL Server

I am trying to update the time only in my datetime column in SQL Server.

I have these columns in my tblAttendance2

DateTimeIn, DateTimeOut, UserID

I want to update the time for my time in and time out on a specific day (WHERE).

Thanks

Upvotes: 3

Views: 9812

Answers (2)

Zoyeb Shaikh
Zoyeb Shaikh

Reputation: 334

 if OBJECT_ID('tblEmployee','U') is not null
 drop table tblEmployee
 go

 create table tblEmployee
 (
 Id int not null primary key identity(1,1),
 Name nvarchar(50),
 DateOfBirth datetime
 )


 insert into tblEmployee values('Sam','1990-08-21')
 insert into tblEmployee values('janny','1995-02-01')
 insert into tblEmployee values('Jhon','2000-03-04')
       
update tblEmployee set DateOfBirth =      
dateadd(second,86399,convert(datetime,DateOfBirth))

select * from tblEmployee

get the result for 23 Hours : 59 Minutes : 59 Seconds to Seconds conversion ie., 86399 Seconds

Upvotes: 0

James Smith
James Smith

Reputation: 1112

This should work:

UPDATE tblAttendance2 
   SET DateTimeIn= CONVERT(varchar(10),DateTimeIn, 120) + ' 12:34:56'
   WHERE DateTimeIn 
   BETWEEN '2012-06-07' AND '2012-06-07 23:59:59'

... edited.. I had MySQL syntax and not SQL2008 originally.

Upvotes: 6

Related Questions