CODEWITHSUNDEEP
sqlsql-server-2005t-sqlsql-server-2008format
user728885
user728885

Reputation:

Format() function doesn't work?

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!

Upvotes: 14

Views: 57016

Answers (4)

T_D
T_D

Reputation: 3301

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

Upvotes: 1

RW5207
RW5207

Reputation: 31

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.

Upvotes: 3

Tom H
Tom H

Reputation: 47464

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.

Upvotes: 5

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56172

Use Convert function instead. Example:

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

Upvotes: 13

Related Questions