CODEWITHSUNDEEP
sqlsql-server-2008datetime
user2612969
user2612969

Reputation: 67

Update Time part of DateTime in SQL

I want to update the time part of my datetime field in SQL. I have an attendance table wherein the User can update the time depending on which attendance date he/she picks. How can I do this?

Upvotes: 0

Views: 321

Answers (2)

sree
sree

Reputation: 1960

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(CAST(MyDate AS DATE) AS DATETIME))

Or this

UPDATE MyTable 
SET MyDate = DATEADD(HOUR, 4, CAST(FLOOR(CAST(MyDate AS FLOAT)) AS DATETIME))

or this 

UPDATE tblAttendance2 
SET DateTimeIn = DateTimeIn + cast(@TimeInChange as DateTime),
DateTimeOut =DateTimeIn + cast(@TimeOutChange as DateTime)
WHERE AttendanceDeductionsID = @AttendanceDeductionsID

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

Here you go..myDate is the field name in the table. I used variables to save time values. Please let me know if you need any clarification.

declare @TestTime datetime
declare @hour int=10
declare @min int=10
declare @sec int=12
select @TestTime = cast(convert(varchar(100),myDate,101)+' 00:00:00' as datetime) from      TestTable
select dateadd(ss,@sec,dateadd(m,@min,dateadd(hh,@hour,@TestTime)))

Upvotes: 1

Related Questions