How to format datetime as M/D/YYYY in SQL Server?
34,068
Solution 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')
Solution 2
DECLARE @datetime DATETIME = '2015-01-01';
SELECT STUFF(REPLACE('/' + CONVERT(CHAR(10), @datetime, 101),'/0','/'),1,1,'')
This is how it works:
- First CONVERT the DATETIME to CHAR
- Then Add a '/' character at the begining
- REPLACE all '/0' with '/'
- With STUFF, get rid of the first '/'
Author by
pkr
Updated on August 19, 2020Comments
-
pkr almost 4 years
To convert a
datetime
toMM/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 to1/1/2015
instead?Nothing on http://www.sql-server-helper.com/tips/date-formats.aspx matches the
M/D/YYYY
format.