Update Date Field

Question

Post reply

Update Date Field

rsteeves-683406

Right there with Babe

Points: 717
More actions
January 8, 2009 at 9:50 am

#127872

I need to update a date field for all records in a table to change the year from 2008 to 2009. I have tried thsi SQL with no success for just one record. THe execution of the SQl says it completed successfully, but it does not change the data.
update tmpDecember_Commissions set
OrderDate = Replace(cast(OrderDate as varchar(50)),'/2008','/2009')
where OrderDate = '10/1/2008 12:11:00 PM'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

User DATEADD(datepart, number, date) function for this operation.

Regards,
Nitin

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

rsteeves (1/8/2009)
I need to update a date field for all records in a table to change the year from 2008 to 2009. I have tried thsi SQL with no success for just one record. THe execution of the SQl says it completed successfully, but it does not change the data.
update tmpDecember_Commissions set
OrderDate = Replace(cast(OrderDate as varchar(50)),'/2008','/2009')
where OrderDate = '10/1/2008 12:11:00 PM'

Check out DATEADD in BOL. Your query will look something like

UPDATE table SET datecolumn = DATEADD(yy, 1, datecolumn)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vick.ram79 SSCommitted Points: 1637 More actions · Answer 3

Use DateADD

Like:

declare @cur_date datetime

select @cur_date = GetDate()

print @cur_date

set @cur_date = dateadd(yyyy, 1, @cur_date)

print @cur_date

How To Post[/url]

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

DATEADD(YY,1,MyOldDate) is the way to go to update the year, but you should be aware that dates of 2008-02-29 wirll be converted to 2009-02-28, because there is no Feb 29 in 2009.