FORMAT function not working in sql server 2008 R2

Question

DECLARE @d DATETIME = '01/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS US_Result;

I am using above code in SQL Server 2008 R2 but it is encountering an error:

'FORMAT' is not a recognized built-in function name.

How can I use FORMAT function?

Answer 1

You can use this:

    select convert(varchar(12) , @d , 3)

You can find here http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx more standard date formats.

Answer 2

FORMAT function is available from version 2012 onwards. In earlier versions, use this:

DECLARE @d DATETIME = '01/01/2011'; 
SELECT replace(replace(' '+convert(varchar(10),@d,101),' 0',''),'/0','/')

However, formatting is the job of the front end application.

Answer 3

According to: FORMAT Function (DAX), (SQL Server 2008 R2), the FORMAT() function exists in SQL Server 2008 R2...

Edit: As pointed out, the above link is related only to DAX. The correct one (FORMAT (Transact-SQL)) specifies that FORMAT, in T-SQL, is only available starting from SQL Server 2012...