Sql-server – Concatenating Strings & Using FORMAT Together – NOT WORKING

formatsql server

I'm literally pulling my hair out trying to use the FORMAT function to format a date into a simple string. Please someone pull the pin and tell me what stupid thing I'm missing!

Works:

SELECT TOP 10 FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Works:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR FROM REV ORDER BY REV_ID DESC

Does NOT Work:

SELECT TOP 10 'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10) FROM REV ORDER BY REV_ID DESC

I can't figure out how to simply concat a string onto the end of the output of FORMAT.

Best Answer

It doesn't work because you are attempting to concatenate the last two characters after specifying the column alias. Move the AS VARCHAR to after the + CHAR (10).

You currently have:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') AS VARCHAR + CHAR(13) + CHAR(10)

when instead it should be:

'Posted on ' + FORMAT(REV_DATE_INSERTED, 'MMMM dd, yyyy') + CHAR(13) + CHAR(10) AS [VARCHAR]

P.S. Please note that I added square brackets around the column name alias. It is a good habit to get into doing that always. It clearly isn't always necessary, but is required when using spaces, most punctuation, etc. And it also avoids potential issues when using reserved keywords (such as VARCHAR, though that isn't causing any problems at the moment).

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:

DECLARE @x TABLE 
(
  id INT, 
  row_num INT, 
  customer_code VARCHAR(32), 
  comments VARCHAR(32)
);

INSERT @x SELECT 1,1,'Dilbert','Hard'
UNION ALL SELECT 1,2,'Dilbert','Worker'
UNION ALL SELECT 2,1,'Wally','Lazy';

SELECT id, customer_code, comments = STUFF((SELECT ' ' + comments 
    FROM @x AS x2 WHERE id = x.id
     ORDER BY row_num
     FOR XML PATH('')), 1, 1, '')
FROM @x AS x
GROUP BY id, customer_code
ORDER BY id;

If you have a case where the data in comments could contain unsafe-for-XML characters (>, <, &), you should change this:

     FOR XML PATH('')), 1, 1, '')

To this more elaborate approach:

     FOR XML PATH(''), TYPE).value(N'(./text())[1]', N'varchar(max)'), 1, 1, '')

(Be sure to use the right destination data type, varchar or nvarchar, and the right length, and prefix all string literals with N if using nvarchar.)

Sql-server – Altering strings while selecting them

DECLARE @x TABLE(StrippedLogin VARCHAR(255));

INSERT @x(StrippedLogin)
  SELECT LEFT([Login], CHARINDEX(':', [Login])-1)
  FROM dbo.OriginalTable
  WHERE CHARINDEX(':', [Login]) > 1;

Best Answer

Related Solutions

Combine Columns from Multiple Rows into Single Row in SQL Server

Sql-server – Altering strings while selecting them

Related Question