Convert DATE_FORMAT to SQL Server function

Question

Have migrated my database from mysql to SQL Server. When I run my query I get the error:

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

This is the query, where I am trying to convert the date as it's saved in the database 2014/03/03 to 03/03/2014 (D-M-Y).

This is the query:

DATE_FORMAT(routines.date, '%d/%m/%Y') as Dato

My apologies if I have misunderstood you, but I sincerely hope you are not storing dates and/or times in SQL Server as VARCHAR (aka strings). Choose an appropriate data type (DATE, DATETIME, SMALLDATETIME, etc) and perform date formatting at the application level. — Fuck StackOverflow
– Fuck StackOverflow, Commented Apr 11, 2014 at 14:20
Store them as DATE!!!!! The format of how they are stored is completely irrelevant. Change that in the display layer. — Zane
– Zane, Commented Apr 11, 2014 at 14:37

Nae · Accepted Answer · 2019-07-10 08:31:41Z

8

Use this:

select CONVERT(varchar(12),getdate(),105)

See this for the various options.

edited Jul 10, 2019 at 8:31

Nae

15.5k8 gold badges62 silver badges89 bronze badges

answered Apr 11, 2014 at 14:17

Sparky

15.1k2 gold badges34 silver badges45 bronze badges

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

5 Comments

user3270211 Over a year ago

That worked. How can I convert the date to show as D/M/Y istead of D-M-Y ?

Sparky Over a year ago

Use 103 instead of 105. Check the link out, it shows you all of the values for formatting the date

Zane Over a year ago

@user3270211 only change that in the display layer. Store the date and time as DATE, TIME, or DATETIME the format does not matter.

TechNerdXp Over a year ago

how to specify column name like created_at, updated at?

Sparky Over a year ago

select CONVERT(varchar(12),getdate(),105) AS Created_at

Orlando Herrera · Accepted Answer · 2014-04-11 16:59:47Z

2

I like to use it in the beginning of my query

SET DATEFORMAT DMY --Day/Month/Year ... you can write YMD or another combination

Syntax:

SET DATEFORMAT { format | @format_var }

You can see examples in microsoft techNet

If you want to use CONVERT in your SELECT clause you can use:

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM) – Oct  2 2008 11:01AM          
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2008                  
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02  
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy

edited Apr 11, 2014 at 16:59

answered Apr 11, 2014 at 14:32

Orlando Herrera

3,5491 gold badge36 silver badges45 bronze badges

Collectives™ on Stack Overflow

Convert DATE_FORMAT to SQL Server function

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related