How to Update the Date without changing its Time Using SQL Server?

I have One Column in the Mytable called StockDate and its DataType is DateTime.

So My Table contains record 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 the StockDate as "2011-09-30". But the time should be same as it is. How to do this? Please I need all your sug开发者_C百科gestions.

Work out the different in whole days and subtract that...

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

Note the use of the yyyymmdd for SQL Server.

You can use dateadd, datediff functions.

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

You could probably use DATEDIFF and DATEADD to get yourself to 9/30/2011 without changing the time.