Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Format date with full month name?

Posted on 2004-07-30
1
Medium Priority
?
3,622 Views
Last Modified: 2008-01-09
I have the field 'MESSAGE_DATE' in a table that is a 'datetime' type, containing data like this:
10/21/2003

I would like a SQL function that will format the date like this:
October 21, 2003

If I use:
SELECT CONVERT(varchar, message_date, 107) FROM my_table
I get the following:
Oct 21, 2003

However, I want the full month name.  Is there an easy function for doing this, similar to MySQL:
SELECT DATE_FORMAT(message_date, '%M %e, %Y');

I have constructed the following, and it works, but it's a bit wordy and I'm wondering if there is a more concise way to format a date in the format I want:
SELECT DATENAME(month, message_date) + ' ' + CAST(DAY(message_date) AS varchar) + ', ' + CAST(YEAR(message_date) AS varchar) AS message_date FROM my_table

Ideas on how to make that shorter, if possible?
0
Comment
Question by:hendridm
1 Comment
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 1050 total points
ID: 11680677
Not directly, AFAIK; that is somewhat odd.  This is the shortest way I know of:

SELECT DATENAME(MONTH, message_date) + SUBSTRING(CONVERT(VARCHAR(30), message_date, 107), 4, 20)
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question