tricky query

[k108]

Hi,

How can I write this?

I have 2 tables, table A and table B. Each table has a datetime field. What I need to do is update just the YEAR portion of table A's datetime with the YEAR from table B's datetime.

So far I have this:

begin tran
update customers c
set renewaldate = CAST( select year(tc.renewaldate) from _temp_customers tc ... ??) AS DATETIME
rollback

Any ideas??

Thanks

 

[SQLBill]

Is each row being set to the same year? Or could rows have different years?

If there could be different years, how do you match the rows?

Can you provide the schema for the two tables?

-SQLBill

Posting advice: FAQ481-4875

 

[SQLBill]

One inelegant way would be to use DATEDIFF(yy, tablea, tableb) to get the difference in years, save it to a variable (like @datechange), then use DATEADD(yy, @datechange, tablea).

-SQLBill

Posting advice: FAQ481-4875

 

[k108]

I think I need a cursor to do this

 

[vongrunt]

Why?

Regarding year stuff, write function a la VB DateSerial:

create function fn_dateSerial( @y smallint, @m tinyint, @d tinyint )
returns smalldatetime
as
begin
	return DATEADD(dd, @d-1, DATEADD(mm, @m-1, DATEADD(yy, @y-1900, 0)))
end

Btw. what to do with Feb 29th dates? :>

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[brian8655]

how are these two tables linked? can you join table a to table b on some key?