How to format datetime as M / D / YYYY in SQL Server?

Question

To convert a datetime to MM/DD/YYYY , this works:

 declare @datetime datetime = '2015-01-01' select convert(varchar(10),convert(date,@datetime),101) 

This value is on 01/01/2015 . How can I convert the date instead of 1/1/2015 ?

Nothing at http://www.sql-server-helper.com/tips/date-formats.aspx matches the M/D/YYYY format.

Answer 1

I think the only opportunity you have is to do something like this:

 DECLARE @datetime DATETIME = '2015-01-01' SELECT LTRIM(STR(MONTH(@datetime))) + '/' + LTRIM(STR(DAY(@datetime))) + '/' + STR(YEAR(@datetime), 4) 

With SQL Server 2012 and above, you can do this:

 SELECT FORMAT(@datetime, 'M/d/yyyy') 

Answer 2

 DECLARE @datetime DATETIME = '2015-01-01'; SELECT STUFF(REPLACE('/' + CONVERT(CHAR(10), @datetime, 101),'/0','/'),1,1,'') 

Here's how it works:

• First convert DATETIME to CHAR
• Then add the character '/' at the beginning
• REPLACE all '/ 0' with '/'
• Use STUFF to get rid of the first '/'

Answer 3

There is no convertible style that uses one day or month. It might work.

 declare @datetime datetime = '2015-01-01' select cast(month(@datetime) as varchar(2)) + '/' + cast(day(@datetime) as varchar(2)) + '/' + cast(year(@datetime) as varchar(4))