Cafer Elgin
Cafer Elgin

Reputation: 423

How to change only the time of a datetime field for a specific time between two dates

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

Answers (2)

Jason
Jason

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

Gordon Linoff
Gordon Linoff

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

Related Questions