Click here to Skip to main content
15,893,381 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
hi I want to update a column only the date without the time change
pleas help me
Posted
Comments
Er. Puneet Goel 5-Apr-14 4:12am    
like same column having datetime '2014-10-10 10:12:33' and you want to change the date only from it? like '2014-10-11 10:12:33' ?

Try this:

SQL
UPDATE
   Yourtable
SET
   datetimecol = DATEADD(day, DATEDIFF(day, datetimecol, getdate()), datetimecol)
 
Share this answer
 
If you mean that you have a column containing DateTime information, and you want to change the date portion and leave the time unchanged, that's quite cumbersome, because there is no easy way to do it.
SQL
UPDATE MyTable SET myDateColumn=CONVERT(DATETIME,'2014-02-12') + CAST(myDateColumn AS TIME)
 
Share this answer
 
SQL
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
 
Share this answer
 
Let say the new date is current date using getdate()
we can do this to get the date part:
convert(varchar, getdate(), 101)

we can also do this to get the time part of the old date time:
convert(varchar, datetimefield, 114)

So the idea is to concatenate the date part of a new date time with the time part of the old date time:
SQL
update table1 set datetimefield =
(convert(varchar, getdate(), 101) + ' ' + convert(varchar, datetimefield, 114))

As for the meanings of 101 and 104, refer this for more detail:
sql-convert[^]
 
Share this answer
 
SQL
For example pls find below
update employees
set date_of_join = dateadd(dd,1,convert(varchar(10),date_of_join,102))+convert(varchar(10),date_of_join,108)
where employeeid = 101
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900