Concatenating Strings & Using FORMAT Together - NOT WORKING

Question

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.

Answer 1

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).