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

Question

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?

Update the older servers to be on a supported version. FORMAT was introduced a few versions ago; all currently supported versions have it. — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Oct 7, 2020 at 7:38
FORMAT isnt a particularly performant function though, you're better off with CONVERT and style codes. — Thom A
– Thom A ♦, Commented Oct 7, 2020 at 7:42
Just avoid running FORMAT on millions of rows and you won't notice the difference. If you are formatting something for human consumption likely the number of rows will be small anyway — Martin Smith
– Martin Smith, Commented Oct 7, 2020 at 7:53

gotqn · Accepted Answer · 2020-10-07 07:44:02Z

6

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.

answered Oct 7, 2020 at 7:44

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jps · Accepted Answer · 2020-10-07 08:35:02Z

1

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

edited Oct 7, 2020 at 8:35

jps

22.9k18 gold badges92 silver badges121 bronze badges

answered Oct 7, 2020 at 8:12

JonWay

1,7471 gold badge21 silver badges48 bronze badges

1 Comment

BeardOverflow Over a year ago

Works for ancient SQL Server versions like 2008. Magic number 121 = yyyy-mm-dd hh:mi:ss.mmm (24-hour) See others here: learn.microsoft.com/en-us/sql/t-sql/functions/…

Gordon Linoff · Accepted Answer · 2020-10-07 12:06:52Z

0

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), '  ', ' ')

answered Oct 7, 2020 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related