1

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.

  • Define "does not work". – mustaccio Sep 21 '16 at 18:31
3

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

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.