Update Date of datetime field without changing time

ITBenelux used Ask the Experts™
Hi does anyone know how to update a datepart?
or update the date

update table set datepart(dd, tablename) = '30'

What I want to do is udpate records date data but only the day
so where all of the records > '2009-07-25 00:00:00.000'
are updated to 30 i.e. 2009-07-30, I want to leave the timestamp portion alone in the records.

Thanks in advance.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

you can use dateadd function for this

Update table_name set field_name = DATEADD(d, 5, field_name) where field_name > '7/27/2009'

msdn link:


I see, this code will add 5 day's.. I should used the real situation, my fault.
due to a development issue in the access front end.  all dates are set to 1/1/1900. I can update them based on the departure date, since these need to be the same.  
(see image)

Question is, if this is doable with the dateadd function ? My first intent was to update via datapart every part of the date without the risk to loose the time settings
Hi you can use below query to do this

UPDATE tableName
SET Arrival_H = DATEADD(dd, 0, DATEDIFF(dd, 0, Departure_H))  + Arrival_H

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial