CODEWITHSUNDEEP
sqlsql-server
bapster
bapster

Reputation: 161

How to update time part in datetime variable

I have a column of type datetime. The time part of the datetime is 00:00:00.000 for all the records. How can I update all of the records so the time is set to 17:59:59.000 without altering the part that contains the date?

Thank you in advance

Upvotes: 0

Views: 424

Answers (4)

Feacio
Feacio

Reputation: 80

INSERT INTO table(datetimefield, ....) VALUES ('2019-10-03 00:00:00', ...);
UPDATE table SET datetimefield = DATETIMEFROMPARTS(YEAR(datetimefield), MONTH(datetimefield), DAY(datetimefield), 17, 59, 59, 0)

Upvotes: 2

Peter Smith
Peter Smith

Reputation: 5550

You could apply DATEADD three times.

DECLARE @myDate DateTime = '2019-01-09'
SELECT @myDate
SELECT @myDate = DATEADD(SECOND, 59, DATEADD(MINUTE, 59, DATEADD(HOUR, 17, @myDate)))
SELECT @myDate

This produces

2019-01-09 00:00:00.000

2019-01-09 17:59:59.000

Upvotes: 2

Nino
Nino

Reputation: 7115

You can use DATEADD()

UPDATE 
    YourTable 
SET 
    datecolumn = DATEADD(SECOND, 59, DATEADD(HOUR,17,DATEADD(MINUTE,59,datecolumn)))
WHERE 
    ID=10000

replace ID, YourTable and DateColumn with your table/column names

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

do update where you got the time 00:00:00.000

create table t ( d datetime);
insert into t values('2019-10-01 00:00:00.000')
insert into t values('2019-10-01 00:00:00.000')

update 
t1
set d= d+ cast('17:59:59.000' as datetime)
from t t1
where cast( d as time)='00:00:00.000'

demo

Upvotes: 2

Related Questions