LISTING 5: Concatenating a String to Display a Verbose Client Message
-- Option #1
-- Using DAY() for Day of Month (with CONVERT() )
-- Using DATENAME() for Month as a string
-- Using DATEPART() for other components (with CONVERT() )
SELECT 'This order was placed on the ' +
      CONVERT(varchar(2), DAY(getdate())) +
      CASE
             WHEN DAY(getdate()) IN (1, 21, 31) THEN 'st'
             WHEN DAY(getdate()) IN (2, 22) THEN 'nd'
             WHEN DAY(getdate()) IN (3, 23) THEN 'rd'
             ELSE 'th'
      END + ' day of ' + DATENAME(Month, getdate()) + ', '
             + CONVERT(char(4), DATEPART(Year, getdate())) + '.'
             AS 'Order Placed On DayOfMonth'
GO
-- Option #2
-- Using DATENAME() for all string components
SELECT 'This order was placed on the ' +
       DATENAME(dd, getdate()) +
       CASE
             WHEN DAY(getdate()) IN (1, 21, 31) THEN 'st'
             WHEN DAY(getdate()) IN (2, 22) THEN 'nd'
             WHEN DAY(getdate()) IN (3, 23) THEN 'rd'
             ELSE 'th'
      END + ' day of ' + DATENAME(Month, getdate()) + ', '
             + DATENAME(Year, getdate()) + '.'
             AS 'Order Placed On DayOfMonth'
GO