monkeylizard
MIS
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:
But apparently I can't use the "+" with text.
So I tried the following, knowing that the date format would be different:
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:
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.
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.
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:
Code:
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:
Code:
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:
Code:
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.
Code:
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.