How to Format Datetime as M/D/Yyyy in SQL Server

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

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

M/D/YYYY h:mm tt to DATETIME SQL Server

The string manipulation is being done to convert it into a recognised ISO standard format. Without doing that, a mistake could be made by doing CAST/CONVERT in confusing the days and months, since different cultures use different date formats. For example 12/2/2015 could represent December 2nd or 12th February.

The answer provided creates a string in the ISO format "YYYY-MM-DD HH:mm:ss". The first line after the SELECT extracts the year and appends a hyphen, the line after that does the month and hyphen, and so on. I'm assuming you spotted the part in that article where the OP pointed out the mistake in the accepted answer.

I assume you know what each individual function does (if you don't, you can search for the definitions on MSDN - do that now, or what follows won't make sense). However, it will probably help to explain some of the patterns being used in the solution when those functions are being used together.

CHARINDEX('/',REVERSE(notADateTime)) is looking at the string back-to-front and finding the first / (and since the string is backwards, that is the last /). So when used in conjunction with 'RIGHT', RIGHT(notADateTime,CHARINDEX('/',REVERSE(notADateTime))-1) is returning the right hand end of the string after the final /.

RIGHT('00'+*value*,2) is being used to ensure certain values (like hours, minutes, days and months) have two digits. The 2 means they want the final two characters (which will be digits, since this is a number). Note that '00' only needs to be '0', since any integer always has one digit already.

Once it's in that format wrap that in a CAST to DATETIME2, so it isn't a string any more.

Now you have this understanding you should be able to have a go at solving this yourself. Remember that solution expected seconds, which your strings don't have. Let me know if you need more help by adding a comment.

how to convert date to a format `mm/dd/yyyy`

As your data already in varchar, you have to convert it into date first:

select convert(varchar(10), cast(ts as date), 101) from <your table>

In SQL Server, how to convert a date to a string in format M/D/YYYY? (no leading zeros, not MM/DD/YYYY)

This is how I would do it:

DECLARE @dt datetime
SET @dt= Citation.PublishedOn
SELECT LTRIM(STR(MONTH(@dt)))+'/'+LTRIM(STR(DAY(@dt)))+'/'+STR(YEAR(@dt),4)

You select your date, then extract the day, month and year from it and chop the leading zeroes off the month and day using ltrim().

If you don't want to declare a variable, you can do this

SELECT LTRIM(STR(MONTH(Citation.PublishedOn)))+'/'+LTRIM(STR(DAY(Citation.PublishedOn)))+'/'+STR(YEAR(Citation.PublishedOn),4)

However, that would mean pulling out the same value multiple times.

How to get a date in YYYY-MM-DD format from a TSQL datetime field?

SELECT CONVERT(char(10), GetDate(),126)

Limiting the size of the varchar chops of the hour portion that you don't want.

Date format returned as mm/dd/yyyy hh:mm:ss AM/PM

Since you're on SQL 2012 the format function should work:

declare @date datetime = '2014-09-26 11:04:54'
select FORMAT(@date,'MM/dd/yyyy hh:mm:s tt')

result: 09/26/2014 11:04:54 AM

In your case it would be:

Select FORMAT(EntryDate,'MM/dd/yyyy hh:mm:s tt')
From DB1

Convert m/d/yyyy H:MM:SS am/pm to month d, yyyy in SQL

If SQL Server 2012+ you can use the format() function

select format(cast('7/4/2016 5:00:00 Am' as datetime),'MMMM d, yyyy')

Returns 
July 4, 2016

Convert to Datetime MM/dd/yyyy HH:mm:ss in Sql Server

Supported by SQL Server 2005 and later versions

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 
       + ' ' + CONVERT(VARCHAR(8), GETDATE(), 108)

* See Microsoft's documentation to understand what the 101 and 108 style codes above mean.

Supported by SQL Server 2012 and later versions

SELECT FORMAT(GETDATE() , 'MM/dd/yyyy HH:mm:ss')

Result

Both of the above methods will return:

10/16/2013 17:00:20

---

Related Topics