CODEWITHSUNDEEP
sqlsql-servert-sql
pkr
pkr

Reputation: 1761

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

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.

Upvotes: 8

Views: 44506

Answers (3)

sqluser
sqluser

Reputation: 5672

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 '/'

Upvotes: 5

Donal
Donal

Reputation: 32713

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')

Upvotes: 20

Jaaz Cole
Jaaz Cole

Reputation: 3180

There's no convert style that uses single digit day or month. This could work, though.

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))

Upvotes: 0

Related Questions