change date values in db

[sunshine9]

Hello,

I have a temp table and i have a date column. i want to use the DATEADD(day, 1, messagedate) method to REPLACE the existing data in the table.

i'm trying something like this, but it is wrong.

DECLARE @mydate datetime
use tempdb

update ##mtable
set @mydate = dateadd(day, 1, messagedate)
set messagedate = @mydate


or i tried this:
set messagedate = dateadd(day, 1, messagedate)

can someone please tell me what i'm doing wrong, or how i can fix this?

thanks in advance...

 

[tlbroadbent]

You should be able to update the table directly using the function.

update ##mtable
set messagedate = dateadd(day, 1, messagedate) Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SQLBill]

Sunshine9,

What error are you getting? Or what results are you getting and what do you want to get? You say 'it's wrong' but you don't say what or why it's wrong.

One thing that might be happening, is that tables in tempdb don't 'stay around' forever. They are dropped when the session that created them is ended. It's possible that what you are using to run your scripts (you don't say what it is) may be disconnecting and reconnecting. Check out the BOL for subject 'create table' and scroll down to the section on temporary tables.

-SQLBill

 

[sunshine9]

Thanks for the help,

i did get this to work:
update ##mtable
set messagedate = dateadd(day, 1, messagedate)

i think there were some extra chars that were 'confusing' query analyzer.

thanks again.