Format() function doesn't work?

Question

I am trying to execute following built-in function in sql but it gives me error that this function doesn't exist

my query:

select EmpId, EmpName, format(EmpJoinDate, "YYYY-DD-MM") as date from Employee

Error i am getting:

'format' is not a recognized built-in function name

What may be the problem, or what am i doing wrong?

Thanks!

score 13 · Accepted Answer · answered Jun 24 '11 at 15:04

13

Use Convert function instead. Example:

select convert(varchar(5), GETDATE(), 126) + convert(varchar(5), GETDATE(), 105)

answered Jun 24 '11 at 15:04

Kirill Polishchuk

54,804
11
122
125

Because it does not exist in SQL Server, are you thinking of Access? – Alex K. Jun 24 '11 at 15:09
Because as the error message says, it's not a valid function. – Ray Jun 24 '11 at 15:09
@user, I don't see `format` on the list of t-sql functions at http://msdn.microsoft.com/en-us/library/ms189826(SQL.90).aspx, so I don't expect that it would work. Are you perhaps thinking of another database? – dsolimano Jun 24 '11 at 15:09
i was looking at this [site](http://www.w3schools.com/sql/sql_func_format.asp) and i tried using format() but it didnt work. is w3schools wrong? – Jun 24 '11 at 15:14
2

Yes. w3schools is terrible; see http://w3fools.com/ - whatever SQL dialect its talking about is not T-SQL. – Alex K. Jun 24 '11 at 15:16
@polishchuk: i m sorry but i didnt downvoted you, i dont have that privilege yet. – Jun 24 '11 at 15:21
@ Alex K: thanks! i should have read SQL book instead of w3school. – Jun 24 '11 at 15:23
@user728885, I'm sorry, I got heated – Kirill Polishchuk Jun 24 '11 at 21:25
8

Format is in T-SQL from version 2012 on.http://msdn.microsoft.com/en-us/library/hh213505(v=sql.110).aspx – Juanjo Sep 29 '14 at 15:18

score 5 · Answer 2 · answered Jun 24 '11 at 15:08

That's because FORMAT() is not a built-in function in SQL 2005. You need to use the CONVERT() function:

SELECT
    EmpId,
    EmpName,
    REPLACE(CONVERT(VARCHAR(10), EmpJoinDate, 102), '.', '-') AS date
FROM
    Employee

A few caveats though... "date" is a reserved word I believe, so I wouldn't suggest using that, even as a column alias. Also, the above actually gives YYYY-MM-DD. YYYY-DD-MM is pretty rare I believe. If you truly want that you'll need to either concatenate things together yourself with the DATEPART and CAST functions or concatenate things using SUBSTRING with CONVERT.

Do a search on CONVERT and you should be able to find all of the various formats supported. The one that I used above (102) is for YYYY.MM.DD, so I did a replace to give the hyphens instead.

Why bother replacing when there's a `convert(varchar(10), EmpJoinDate, 23)` to format straight to "YYYY-DD-MM"? I kinda expected that to be the most popular answer... — T_D, May 30 '17 at 12:48

score 3 · Answer 3 · answered Oct 31 '11 at 16:55

3

According to MSDN documentation, http://msdn.microsoft.com/en-us/library/hh213505(SQL.110).aspx, it is a built-in function. I am assuming it has not been implemented.

answered Oct 31 '11 at 16:55

RW5207

31
1

6

That page is specifically for SQL Server "Denali" (now officially SQL Server 2012) as indicated immediately below the title: if it was supported in other versions this text would be a dropdown. – onedaywhen Nov 01 '11 at 09:52

score 1 · Answer 4 · edited May 23 '17 at 12:30

1

The other answers will work but use some hacky string replace and concats. Although you could set date_style to 23 to convert straight to "YYYY-DD-MM" format:

convert(varchar(10), EmpJoinDate, 23)

For a complete list of formats I'd like to refer to this awesome SO-answer: https://stackoverflow.com/a/19537658/2140636

edited May 23 '17 at 12:30

Community

1
1

answered Feb 08 '16 at 11:21

T_D

3,241
3
17
24

Format() function doesn't work?

4 Answers4