Reputation: 423
I have a table in SQL Server that stores many entries with datetime
columns. I want to change time from 08:30:00:000 to 08:00:00:000 between the dates 2017-06-01 and 2017-06-30.
Example data is below:
ID FIELD1 FIELD2 START_DATETIME END_DATETIME
--------------------------------------------------------------------------------
18936 48 2 2017-06-01 19:00:00:000 2017-06-02 08:30:00:000
18937 5 2 2017-06-01 19:00:00:000 2017-06-02 08:30:00:000
18938 4 2 2017-06-01 19:00:00:000 2017-06-02 08:30:00:000
18939 46 2 2017-06-01 19:00:00:000 2017-06-02 08:30:00:000
18940 40 2 2017-06-02 08:30:00:000 2017-06-02 19:00:00:000
18941 16 2 2017-06-02 08:30:00:000 2017-06-02 19:00:00:000
18942 21 2 2017-06-02 08:30:00:000 2017-06-02 19:00:00:000
Thanks.
Upvotes: 1
Views: 396
Reputation: 945
I modified my answer since you want to update the data in the table.
--Change START_DATETIME
UPDATE yourTable --replace with your table name
SET START_DATETIME = (DATEADD(MINUTE, -30, START_DATETIME))
WHERE START_DATETIME > '2017-06-01'
AND
END_DATETIME < '2017-07-01'
AND
CAST(START_DATETIME AS TIME) = '08:30:00'
GO
--Change END_DATETIME
UPDATE yourTable --replace with your table name
SET END_DATETIME = (DATEADD(MINUTE, -30, END_DATETIME))
WHERE START_DATETIME > '2017-06-01'
AND
END_DATETIME < '2017-07-01'
AND
CAST(END_DATETIME AS TIME) = '08:30:00'
GO
Upvotes: 2
Reputation: 1269773
You can use update
. For start_datetime
it would be:
update t
set start_datetime = dateadd(minute, -30, start_datetime)
where start_datetime >= '2017-06-01' and
start_datetime < '2017-07-01' and
cast(start_datetime as time) = '08:00:00';
You can do something similar with end_datetime
. Or both at once:
update t
set start_datetime = (case when cast(start_datetime as time) = '08:00:00'
then dateadd(minute, -30, start_datetime)
else start_datetime
end),
end_datetime = (case when cast(end_datetime as time) = '08:00:00'
then dateadd(minute, -30, end_datetime)
else end_datetime
end)
where start_datetime >= '2017-06-01' and
start_datetime < '2017-07-01' and
(cast(start_datetime as time) = '08:00:00' or
cast(end_datetime as time) = '08:00:00'
);
This assumes that the date range is determined by start_datetime
.
Upvotes: 1