1

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2431727
  • 877
  • 2
  • 15
  • 46
  • 4
    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 Oct 07 '20 at 07:38
  • 2
    `FORMAT` isnt a particularly performant function though, you're better off with `CONVERT` and style codes. – Thom A Oct 07 '20 at 07:42
  • 1
    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 Oct 07 '20 at 07:53

3 Answers3

4

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.

gotqn
  • 42,737
  • 46
  • 157
  • 243
0

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
jps
  • 20,041
  • 15
  • 75
  • 79
JonWay
  • 1,585
  • 17
  • 37
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), '  ', ' ')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786