Add date to TEXT or NVARCHAR field

[monkeylizard]

I need to add some additional text to a text field. We'll call the field NOTES.

Assume that NOTES on a record contains a value of 'This is the first note, added on 04/18/10 4:26:08 PM.' I need to make the field have a new value of:


'This is the first note, added on 04/18/10 4:26:08 PM. This is the second note added on 04/20/10 3:18:24 PM.'

I'm working from within a stored proc. I tried:

DECLARE
@TEXT text
SELECT @TEXT = NOTES FROM NOTES_TABLE
SELECT @TEXT = @TEXT + ' This is the second note added on ' + getdate() +'.'
UPDATE NOTES_TABLE SET NOTES = @TEXT

But apparently I can't use the "+" with text.

So I tried the following, knowing that the date format would be different:

DECLARE
@TEXT nvarchar(4000)
SELECT @TEXT = CONVERT(nvarchar(4000),NOTES) FROM NOTES_TABLE
SELECT @TEXT = @TEXT + ' This is the second note added on ' + getdate() +'.'
UPDATE NOTES_TABLE SET NOTES = @TEXT

I get "Error converting datetime from character string."

So I changed the second SELECT to convert the getdate to char, and I formatted it while I was at it:

DECLARE
@TEXT nvarchar(4000)
SELECT @TEXT = CONVERT(nvarchar(4000),NOTES) FROM NOTES_TABLE
SELECT @TEXT = @TEXT + ' This is the second note added on ' + CONVERT(CHAR(20), CURRENT_TIMESTAMP, 22) +'.'
UPDATE NOTES_TABLE SET NOTES = @TEXT

I don't get an error, but the variable @TEXT becomes empty on the second SELECT. If I print it after the first SELECT, it's OK. It definitely gets the value from the table and loads it to the variable. It's getting cleared when I try to add it to itself.

If I take the date out of the picture and do the following, it works fine.

DECLARE
@TEXT nvarchar(4000)
SELECT @TEXT = CONVERT(nvarchar(4000),NOTES) FROM NOTES_TABLE
SELECT @TEXT = @TEXT + ' This is the second note.'
UPDATE NOTES_TABLE SET NOTES = @TEXT

Any tips on getting the current datetime in there?

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

Nevermind. The code shown in the last block works fine. It was a second variable that I dind't tell you guys about that I was stringing in that was NULL and that was clearing the whole @TEXT variable.


Oops.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.