sql server convert date to string MM/DD/YYYY

Question

I am using SQL Server 2008.

I have the following:

    select convert(varchar(20),fmdate) from Sery

How do I convert the date to string such that it show as MM/DD/YYYY

score 79 · Accepted Answer · answered Aug 07 '12 at 22:13

79

That task should be done by the next layer up in your software stack. SQL is a data repository, not a presentation system

You can do it with

CONVERT(VARCHAR(10), fmdate(), 101)

But you shouldn't

answered Aug 07 '12 at 22:13

TFD

14

There are actually several reasons to have the database do this, including time/date difference at server location, especially if daylight savings factors into it. Yes, it does force what is likely a locale-specific format, though. – zanlok Apr 02 '13 at 01:42
1

@zanlok A locale specific format would generally be a bad idea – TFD Apr 02 '13 at 03:12
8

I would say you don't want to store a formatted date in your database, but if you're for some reason exporting the field (say... to a .csv file) you could have a very valid reason for doing so. – RubberDuck Apr 08 '14 at 13:26
2

There are plenty of reasons to use this. This time my reason being I am using this to format a date for presentation in body of sp_send_dbmail... – Taylor Brown Aug 02 '17 at 21:26

score 29 · Answer 2 · answered Aug 07 '12 at 22:13

29

select convert(varchar(10), fmdate, 101) from sery

101 is a style argument.

Rest of 'em can be found here.

answered Aug 07 '12 at 22:13

Tony Hopkinson

score 4 · Answer 3 · answered May 03 '16 at 12:43

4

select convert(varchar(10), cast(fmdate as date), 101) from sery

Without cast I was not getting fmdate converted, so fmdate was a string.

answered May 03 '16 at 12:43

Kirt Carson

Source- http://stackoverflow.com/questions/18568410/how-to-convert-date-to-a-format-mm-dd-yyyy – Kirt Carson May 03 '16 at 12:44

score 3 · Answer 4 · edited Nov 19 '19 at 00:28

3

edited Nov 19 '19 at 00:28

KyleMit

answered Jan 19 '18 at 15:34

Bhanu Pratap

score 3 · Answer 5 · answered Nov 19 '19 at 00:42

As of SQL Server 2012+, you can use FORMAT(value, format [, culture ])

Example:

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy')

Further Reading:

5 Answers5