Update only date Date DateTime

Question

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

10/4/2012 8:03:00 AM 

How to update only part of the date?

The field name is dTime.

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

Answer 1

 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 

Answer 2

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

Answer 3

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

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

Answer 4

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

Just use varchar and change what you want in it without touching time. In this example, I use CONVERT (varchar (12), columnDatetime) to get a 12-character string that assumes a time case with a format such as "20: 10: 15.250".