How to concatenate a string and GETDATE () in MSSQL

Question

I need to insert a line (comment) that should contain a date. I basically need the following simple operation:

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

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

Quick fixes?

Answer 1

What is the date format you need?

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

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

Answer 2

Depending on the definition of the column, you may try to 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, for example

  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 the data, you can only store the datetime in a column by adding Date text using the SELECT statement

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

Answer 4

If one of the output fields is NULL, the combined output will be zero. To solve this problem, try

lname + ',' + space (1) + fname + space (1) + (the case when mname is null and 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!