How to concatenate a string and GETDATE() in MSSQL

Question

I need to insert a string (a comment) which should include a date. What I need is basically the following simple operation:

INSERT INTO [Table_1]
           ([textColumn])
     VALUES
           ('Date: ' + GETDATE())
GO

This however, returns the following error: Conversion failed when converting date and/or time from character string.

Any quick fixes?

Answer 1

what is the date time format you need?

select one from here http://www.sql-server-helper.com/tips/date-formats.aspx and convert it to a char as bellow

INSERT INTO [Table_1]
           ([textColumn])
     VALUES
           ('Date: ' +CONVERT(CHAR(10),  GETDATE(), 120))
GO

Answer 2

Depending on the column's definition, you can try to cast or convert the date to the desired type:

INSERT INTO [Table_1]
       ([textColumn])
 VALUES
       ('Date: ' + CAST(GETDATE() as nvarchar(max)))
GO

To format the date, use Convert, e.g.

 INSERT INTO [Table_1]
       ([textColumn])
 VALUES
       ('Date: ' + convert(nvarchar(max), GETDATE(), 101))
 GO

The last Parameter defines the format - see msdn for details.

Answer 3

Instead of adding it as part of data, you can store only datetime in column append the text Date using SELECT statement

select 'Date '+ CAST(GETDATE() as nvarchar(max)) from [Table_1]

Answer 4

In case one of output field is null, the combined output will be null. To solve, try this

lname + ',' + space(1) + fname + space(1) + (case when mname is null then '' else mname end) as FullName

from: http://forums.devshed.com/ms-sql-development-95/concatenate-when-one-column-is-null-371723.html

I tried and it works!