FORMAT function does not work in SQL Server 2008 R2

Question

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

I use the code above in SQL Server 2008 R2, but it encounters an error:

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

How to use the FORMAT function?

Answer 1

The FORMAT function has been available since 2012. 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 application.

Answer 2

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 for more standard date formats.

Answer 3

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

Edit: As stated above, the referenced link is for DAX only. Correct ( FORMAT (Transact-SQL) ) indicates that FORMAT in T-SQL is only available with SQL Server 2012 ...