I would like to be able to update an nvarchar column with GETDATE().

e.g. if the value is currently 'ordered', I need to append the date.

I did try :

update order_line
set comments = 'ordered'+ getdate ()
where ol_number = 10

but I received the error

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

Is there a best method of achieving this please?


You should CAST the date to a string type, otherwise, the + operator tries to make an erroneous implicit cast (it is actually trying to convert 'ordered' into a datetime, to add it to the getdate()):

update order_line
set comments = N'ordered ' + CAST(getdate() AS nvarchar(30))
where ol_number = 10 ;

and will result in:

SELECT * FROM order_line ;
ol_number | comments                   
--------: | :--------------------------
       10 | ordered Jul 29 2017  5:34PM

You can check it at dbfiddle here

| improve this answer | |

So if comments = N'ordered' you want to append GETDATE() ?

UPDATE order_line
SET comments = N'ordered '+ CAST(GETDATE() AS NVARCHAR(30))
where ol_number = 10 AND comments = N'ordered'
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.