Update only Date portion of DateTime

Question

I am using SQL Server 2008. I have a datetime in the database where the value is

    10/4/2012 8:03:00 AM

How do I update only the date portion?

The field name is dTime.

I like to update the date to 10/5/2012 and keep the time as it is.

What type of update do you wanna do? – Aron Einhorn Aug 20 '13 at 19:11 — Aron Einhorn, Aug 20 '13 at 19:11

score 37 · Answer 1 · answered Aug 20 '13 at 19:14

37

UPDATE
    tableX
SET
    columnX = DATEADD( day, DATEDIFF( day, columnX, '20120510' ), columnX ) 
WHERE
    (conditionX) ;

answered Aug 20 '13 at 19:14

ypercubeᵀᴹ

113,259
19
174
235

score 8 · Accepted Answer · answered Aug 20 '13 at 19:35

CREATE TABLE dbo.MyTable(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    DTColumn DATETIME NOT NULL
);
GO
INSERT dbo.MyTable(DTColumn) VALUES 
('20120410 08:03:00.000'),
('20010101 01:01:01.000');

BEGIN TRANSACTION;
    UPDATE  dbo.MyTable
    -- Style 126 / ISO8601 = yyyy-mm-ddThh:mi:ss.mmm        
    SET     DTColumn = STUFF(CONVERT(VARCHAR(50),DTColumn,126) ,1, 10, '2012-05-10') 
    -- Uncomment this line to see the old and new values
    -- OUTPUT   deleted.DTColumn AS OldValue, inserted.DTColumn AS NewValue
    WHERE   CONVERT(DATE,DTColumn)='2012-04-10' 

    SELECT * FROM dbo.MyTable;
ROLLBACK;
-- COMMIT

Results:

OldValue                NewValue
----------------------- -----------------------
2012-04-10 08:03:00.000 2012-05-10 08:03:00.000

ID          DTColumn
----------- -----------------------
1           2012-05-10 08:03:00.000
2           2001-01-01 01:01:01.000

nice use of inserted and deleted tables. – gicalle Feb 10 '17 at 08:32 — gicalle, Feb 10 '17 at 08:32

score 4 · Answer 3 · answered Aug 20 '13 at 19:13

4

UPDATE YourTable
SET YourColumn = DATEADD(MONTH,1,YourColumn) 
WHERE YourColumn = '20120410 08:03:00'

I assumed that you posted your date on DD/MM/YYYY format. If it was MM/DD/YYYY you should use DATEADD(DAY,1,YourColumn) instead.

answered Aug 20 '13 at 19:13

Lamak

69,480
12
108
116

hey, do you mean the first parameter afftected by date format? I think the document says it represent the part of date, and we don't need to care about date format. `DATEADD(MONTH, 1, YourColumn)` add a month to the date, but `DATEADD(DAY, 1, YourColumn)` only add 1 day to the date. – Jun Yu Aug 31 '21 at 07:19

Omar Chavez · Answer 4 · 2016-05-24T19:13:53.817

1

 UPDATE table1 
 SET columnDatetime = '10/5/2012' + ' ' + CONVERT(varchar(12), CONVERT(time, columnDatetime))
 WHERE CONVERT(date, columnDatetime) = '10/4/2012'

Just use varchar and modify what you want in it without touch the time. In this example I use CONVERT(varchar(12), columnDatetime) to get a string with length of 12 characteres assuming a case of time with a format for example like "20:10:15.250".

edited May 24 '16 at 19:13

answered May 24 '16 at 18:32

Omar Chavez

91
1
2

score 0 · Answer 5 · answered Nov 02 '20 at 09:17

0

update tableX set dttime = CONCAT("2012-10-24 ",time(dttime)) WHERE date(dt1)='2012-05-10'

answered Nov 02 '20 at 09:17

Dsqwared

1
5

1

Awww... That poor little piece of code. It looks so scared that people might not like it, so alone and vulnerable. Wouldn't you like to give it some company and protection by adding an explanation? (And maybe some improved formatting...) Please read [answer]. – Yunnosch Nov 02 '20 at 09:22

score 0 · Answer 6 · answered Nov 20 '22 at 05:07

0

Here is my query. It will update signout date with signing date without changing the time.

UPDATE TableName SET SignInTime=STUFF(CONVERT(VARCHAR(50),SignInTime,126) ,1, 10, CONVERT(date, SignOutTime)) where Id = 100

answered Nov 20 '22 at 05:07

Mukesh Salaria

3,345
1
16
21

Update only Date portion of DateTime

6 Answers6

Linked