Solved

Format date with full month name?

Posted on 2004-07-30
1
3,606 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 69

Accepted Solution

by:
Scott Pletcher earned 350 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trying to get a Linked Server to Oracle DB working 21 60
Linked Server Issue with SQL2012 3 26
Query Help - MSSQL - Averages 5 27
Flattening heirachies 3 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

803 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