CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Mike
Mike

Reputation: 843

Replace year in datetime date

I have a table with a column (dateDT), formatted as datetime and a variable input for the year (@selYear) in my Select, formatted as int.

How can I replace the year within a date from my table with the variable input and return the result in datetime format (within a Select) ?

I have tried the following but this returns wrong dates / years: 

CONVERT(DATETIME, (@selYear + MONTH(dateDT) + DAY(dateDT))) AS dateDT,

Example: 

@selYear = 2014
dateDT = 2010-05-02 00:00:00.000

In this case my result should be: 

dateDT = 2014-05-02 00:00:00.000

Upvotes: 6

Views: 17820

Answers (3)

gotqn
gotqn

Reputation: 43636

A solution using DATEADD function:

DECLARE @selYear VARCHAR(4) = 2014
DECLARE @dateDT DATETIME = '2010-05-02 00:00:00.000'


SELECT DATEADD(YEAR,@selYear - YEAR(@dateDT),@dateDT)

This is example with smaller then current year:

DECLARE @selYear INT = 2009
DECLARE @dateDT DATETIME = '2010-05-02 00:00:00.000'


SELECT DATEADD(YEAR,@selYear - YEAR(@dateDT),@dateDT)

Upvotes: 5

t-clausen.dk
t-clausen.dk

Reputation: 44326

You can use DATEFROMPARTS in sqlserver 2012

DECLARE @selYear int = 2014
DECLARE @dateDT datetime = '2010-05-02'
SELECT DATEFROMPARTS(@selYear, 
                     DATEPART(m, @dateDT), 
                     DATEPART(d, @dateDT))

Upvotes: 4

clintperry
clintperry

Reputation: 192

SELECT REPLACE(dateDT, DATEPART(year, dateDT), @selYear)

Upvotes: 2

Related Questions