How to update the date without changing the time of using SQL Server?

Question

I have one column in Mytable called StockDate and its DataType is DateTime.

So my table contains an entry as follows

SID StockDate ----- ------------ 1 2011-10-02 09:44:41.170 2 2011-10-02 09:48:23.234 

Here I want to update only the Date of StockDate as "2011-09-30". But time must be the same as it is. How to do it? Please, I need all your suggestions.

Answer 1

Design different whole days and subtract that ...

 UPDATE MyTable SET StockDate = DATEADD(day, DATEDIFF(day, StockDate, '20110930'), StockDate) WHERE ... 

Pay attention to the use of yyyymmdd for SQL Server.

Answer 2

http://msdn.microsoft.com/en-us/library/ms186724(v=SQL.90).aspx

Perhaps you can use DATEDIFF and DATEADD to get to 9/30/2011 without changing the time.

Answer 3

You can use dateadd , datediff functions .