CODEWITHSUNDEEP
sql-server
Pece G.
Pece G.

Reputation: 29

Update SQL dates in SQL Server 2008

this is what I need: from the saledate column I need to extract just the month and date and combine with the 2017 year in NewDate column, but I couldn't update. Any suggestions? enter image description here

This is the Select statment, I'm trying to update with the alias NewDate and getting an error: The conversion of a varchar data type to a datetime data type resulted in an out of range value. This is the data in the saledate column: 1983-09-01 00:00:00.000, I'm trying to make to be the same, just the year to be 2017.

SELECT saledate, renewaldate,CONVERT(date,saledate), ('2017'+ '-' + LTRIM(REVERSE(SUBSTRING(REVERSE(CONVERT(date,saledate)), 1, 5)))) AS NewDate FROM tprogram

UPDATE tprogram SET renewaldate = ('2017'+ '-' + LTRIM(REVERSE(SUBSTRING(REVERSE(CONVERT(date,saledate)), 1, 5)))) FROM tprogram

Upvotes: 2

Views: 159

Answers (4)

Wes H
Wes H

Reputation: 4439

Just add the difference in years back to the sale date.

SELECT  DATEADD(YEAR,DATEDIFF(YEAR,SaleDate,'20170101'),SaleDate)

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use datefromparts as below:

select SaleDate, RenewalDate, Convert(date, SaleDAte), 
    DATEFROMPARTS(2018, datepart(month,SaleDate),DATEPART(day,SaleDate)) as NewDate 
    from yourtable

Upvotes: 0

BJones
BJones

Reputation: 2460

You say you need it with the 2017 year, but you're using a 2018 value. Here's something to get started.

SELECT CONVERT(DATE,'2017-'+CONVERT(VARCHAR(2),MONTH(SaleDate))+'-'+CONVERT(VARCHAR(2),DAY(SaleDate))) AS NewDate

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

You could use dateadd() with the day() and month() functions like so:

select dateadd(day,day(saledate)-1,dateadd(month,month(saledate)-1,'20180101')) as NewDate

For example:

select dateadd(day,day(getdate())-1,dateadd(month,month(getdate())-1,'20180101'))

returns: 2018-05-16

Upvotes: 1

Related Questions