CODEWITHSUNDEEP
sqlsql-serversql-server-2008
domsca
domsca

Reputation: 51

MS SQL Update Date Only

All,

Is there a way in SQL to update a datetime field and only change the date? I have a year's worth of data in datetime format (YYYY-MM-DD XX:XX:XX.XXX) and would like the set date for every value to be 12/30. I do not want to change the time or the year.

I tried the following but with no luck:

UPDATE table
SET DATEPART(MONTH,[date]) = 12 WHERE DATEPART(YEAR,[date]) = 2016

Any suggestions?

EDIT: Using SQL Server 2008

Upvotes: 2

Views: 699

Answers (2)

John Cappelletti
John Cappelletti

Reputation: 81960

EDIT - UPDATE for 2008 - Convert() and Stuff()

Declare @YourTable table (date datetime)
Insert Into @YourTable values
 ('2017-05-11 13:17:26.350')
,('2016-07-29 09:22:56.350')

Select Date
      ,stuff(convert(varchar(25),[date],25),6,5,'12-30')
 From @YourTable

Returns

Date                      (No column name)
2017-05-11 13:17:26.350   2017-12-30 13:17:26.350
2016-07-29 09:22:56.350   2016-12-30 09:22:56.350

So to apply an update

Update YourTable 
   set [date] = stuff(convert(varchar(25),[date],25),6,5,'12-30')
 Where DatePart(YEAR,[date]) = 2016

Upvotes: 1

Chuck
Chuck

Reputation: 1031

This should work in SQL 2008:

Update table
    set [date] = '12/30/' + cast(datepart(year,[date]) as char(4)) + ' ' + convert(varchar(20),[date],114)
WHERE DATEPART(YEAR,[date]) = 2016

Upvotes: 1

Related Questions