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 evaluates to 01/01/2015. How can I have the date convert to 1/1/2015 instead?

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

Answer 1

I think the only possibility 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,'')

This is how it works:

1. First CONVERT the DATETIME to CHAR
2. Then Add a '/' character at the begining
3. REPLACE all '/0' with '/'
4. With STUFF, get rid of the first '/'