--SYNTAX: - FORMAT (value, format [, culture])

/* Using Format Function */
DECLARE @TodaysDate DATETIME = GETDATE() 
SELECT FORMAT ( @TodaysDate, 'd', 'en-US' ) AS [Using 'en-US' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'de-DE' ) AS [Using 'de-DE' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'en-IN' ) AS [Using 'en-IN' Culture]
SELECT FORMAT ( @TodaysDate, 'd', 'en-CA' ) AS [Using 'en-CA' Culture]
GO
DECLARE @ProductCost INT = 100
SELECT FORMAT ( @ProductCost, 'c', 'en-US' ) AS [Using 'en-US' Culture]
SELECT FORMAT ( @ProductCost, 'c', 'de-DE' ) AS [Using 'de-DE' Culture]
SELECT FORMAT ( @ProductCost, 'c', 'en-IN' ) AS [Using 'en-IN' Culture]
GO

--SYNTAX: - CONCAT (string_value1, string_value2 [,string_valueN])
/* Using CONCAT Function */
Use AdventureWorks2008R2
GO
SELECT
 TOP 10 
 CONCAT( PP.FirstName ,' ',
  PP.LastName ,',',
  PA.AddressLine1 ,',',
  PA.AddressLine2 ,'',
  PA.City ,',',
  PA.PostalCode) 
FROM Person.Address PA
 INNER JOIN Person.BusinessEntityAddress PBEA 
  ON PA.AddressID = PBEA.AddressID 
 INNER JOIN Person.Person PP 
  ON PBEA.BusinessEntityID = PP.BusinessEntityID
GO

SET NOCOUNT ON
SELECT CONCAT('MSSQLTips published this tip on ', GETDATE())
GO
PRINT CONCAT('MSSQLTips published this tip on ', GETDATE())
GO
SET NOCOUNT OFF

Results
---------------------------------------------------
MSSQLTips published this tip on Jan 21 2011 12:00AM
MSSQLTips published this tip on Jan 21 2011 12:00AM