CODEWITHSUNDEEP
sqlsql-server
user2431727
user2431727

Reputation: 907

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

I have a query like below.

SELECT FORMAT (Submitted_time,'dd-MM-yyyy h:mm tt')
FROM header

And I get the expected output on some of the latest SQL Server as :

27-07-2020 9:15 AM

But when executing same query on some other SQL Server machine, it throws an exception

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

How can I solve this?

Upvotes: 1

Views: 20065

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You clearly have a pre-2012 version of SQL Server or a compatibility level set to an earlier version. You may be able to construct the string that you want using:

select replace(convert(varchar(255), getdate(), 105) + ' ' + right(convert(varchar(255), getdate()), 7), '  ', ' ')

Upvotes: 0

JonWay
JonWay

Reputation: 1735

Not all SQL Server versions FORMAT function work You can use convert as below:

DECLARE @Submitted_time datetime 

SET @Submitted_time= GETDATE()

SELECT CONVERT(VARCHAR(30),@Submitted_time , 121)

Output

2020-10-07 09:11:07.923

Upvotes: 1

gotqn
gotqn

Reputation: 43636

The FORMAT function was introduced with SQL Server 2012 and as pointed in the docs it is available in all supported versions.

You are either running it in older version or the compatibility of the database is set to earlier.

Upvotes: 5

Related Questions