Update Date of datetime field without changing time

ITBenelux
ITBenelux used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

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

Commented:
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:
http://msdn.microsoft.com/en-us/library/ms186819(v=sql.90).aspx

Author

Commented:
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
Clipboard01.jpg
Commented:
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