Solved

Format date with full month name?

Posted on 2004-07-30
1
3,609 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
export sql server dbs 2 26
VM SQL server license. 1 67
SQL Server Reports (SSRS 2014) - Reports Timing Out 11 118
Convert DateTime format in SQL query 10 23
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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

751 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