Update date part

[timscronin]

Trying below to update just the year on a field, syntax is incorrect. is there a better way to do this

Update p_lab_results_obr
set year(COLL_DATE_TIME) = year(SPEC_RCV_DATE_TIME)

 

[AlexCuse]

I don't believe you can change only a specific datepart (although I would love to be proven wrong). You will need to use year(SPEC_RCV_DATE_TIME) and year(COLL_DATE_TIME) to build a string and update your date column to taht.

Something like this

set COLL_DATE_TIME = cast(year(SPEC_RCV_DATE_TIME) as Char) 
+ '-' + cast(month(COLL_DATE_TIME) as Char)
+ '-' + cast(day(COLL_DATE_TIME) as Char)
+ ' ' + convert(varchar(8), COLL_DATE_TIME, 108)

(I have tested this, and it does update only the year. It might need to be tweaked for your particular date/time format)

Hope this helps,

ALex



Ignorance of certain subjects is a great part of wisdom

 

[johnscott76]

You could use DateDiff() to get the number of years between your stored date and the new date, then DateAdd() to calculate the new date.

Here's a test script:

First just the setup for the test...

Use tempdb
Go

If Exists ( Select * From sysobjects Where name = 'MyTable' And type = 'U' ) Drop Table MyTable
Go

Create Table MyTable (
  MyTableKey
    int Identity (1,1),
  MyDate1
    datetime,
  MyDate2
	datetime
)
Go

Insert Into MyTable (MyDate1, MyDate2) Values ('2000-04-25', '2007-01-01')
Insert Into MyTable (MyDate1, MyDate2) Values ('2002-10-07', '2007-01-01')
Insert Into MyTable (MyDate1, MyDate2) Values ('1975-08-13', '2007-01-01')
Insert Into MyTable (MyDate1, MyDate2) Values ('2000-07-19', '2007-01-01')
Insert Into MyTable (MyDate1, MyDate2) Values ('2000-06-26', '2007-01-01')
Go

Select * From MyTable
Go

Now the actual update statement:

Update MyTable
  Set MyDate1 = DateAdd(year, DateDiff(year, MyDate1, MyDate2), MyDate1)
Go

Select * From MyTable
Go

Hope this helps