Replace a certain part of the date

Question

We are creating a demo database. So they need me to change a column having all the dates to a particular date.

Replace just the month and day and keep the year as is.

Ex: 03/12/2012, 06/19/1990

Solution: 01/01/2012, 01/01/1990

They want to make the month and date to: 01/01

My Query is as mentione below:

update Tablename set column = REPLACE(column, select substring(column,1,5) from Tablename ,'01/01') ;

but i get an error as mentione below:

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '01/01'.

Any help/advice would be appreciated.

Answer 1

Don't change dates with string manipulation. It is guaranteed to break things down the road and it's slower.

UPDATE TableName
SET [column] = DATEADD(year,DATEDIFF(year,0,[column]),0)

Answer 2

as of SQL Server 2012 there is a DATEFROMPARTS function which is perfect for this ... eg ... with @originalDate DATE, @newMonth INT, @newDay INT you could form your new date like this:

DECLARE @replacedDate Date;

SET @replacedDate = DATEFROMPARTS(
                    DATEPART(year, @originalDate),
                    @newMonth,
                    @newDay
                )

your sql is thus:

update Tablename 
set column = DATEFROMPARTS(DATEPART(year, column),1,1)

here are the sql server docs:

DATEFROMPARTS DATEPART