How to convert date in dd Mon yyyy?

I want to convert date to format dd Mon yyyy such as 14 April 2000. How? The method as simple as possible
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


You can use

<% FullDate = Month(date) & "/" & Day(date) & "/" & Year(date)
' FullDate is the combination of the exact Month, Day and Year.
' It will be used to assign the value of the hidden field. %>

Complete article at

Or read that will provide you with complete explanation.

Hope this helps.
This converts system date to what you want:

select convert(varchar(12), getdate(),106)

Just replace "getdate()"  with your date variable.   If you have any questions, please let me know.
formula, great suggestion !

just my 2 cents: change varchar(12) to varchar(16) to prevent date cutting from the end.


The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.


If you want to display time, I agree.  But the "106" style doesn't include time, so 12 characters is long enough.

I have to say I'm not very familiar with SQL Server so I tested your suggestion and got the following resultsets (this is Finnish):

30 maalis 20  (same as '30 March 2000')
27 tammi 200  (same as '27 January 2000')

so it seems that last one/two zeroes are missing from the year. What's causing this?

Language is the difference. When I do this, I get the following:

30 Mar 2000
27 Jan 2000

In the English character set for SQLServer, the months are always three characters abbreviated for a "106" style.  Looks like in Finnish, it's different.  So I'll revise my statement to "In English, 12 characters is sufficient".  I did not realize you were using a different language.  I suggest making the character field as large as it needs to be for your language character set.

formula, thank you for resolving the difference. How do you get full month name in English regional settings, for eg. '30 March 2000' or '1 December 2000'?

106 seems to view short month name...
Here are all the style possibilities in English for dates:

Style      Result

100   Mar 30 2000  4:01PM      
101   03/30/2000                
102   2000.03.30                
103   30/03/2000                
104   30.03.2000                
105   30-03-2000                
106   30 Mar 2000              
107   Mar 30, 2000
108   Time Only 16:01:40        
109   Mar 30 2000  4:01:40:090PM
110   03-30-2000                
111   2000/03/30                
112   20000330                  
113   30 Mar 2000 16:01:40:090  
114   Time Only 16:01:40:090    

As you can see, months are always abbreviated. No way to get full name, except to program it using datepart functions.
formula, that was good information.

Would you show how to program it - I think the Customer wants the full month name: "... format dd Mon yyyy such as 14 April 2000 ..."

Here's an example.  Copy this code in SQL window and run it to see result:

/* Function for creating specific date format output */

declare @day varchar(2),
        @month varchar(8),
        @year char(4),
        @initialdate datetime,
        @convertdate varchar(16)

select @initialdate=getdate()

select @day=convert(char(2),datepart(dd,@initialdate))
if (select datepart(mm,@initialdate))=1 select @month='January'
if (select datepart(mm,@initialdate))=2 select @month='February'
if (select datepart(mm,@initialdate))=3 select @month='March'
if (select datepart(mm,@initialdate))=4 select @month='April'
if (select datepart(mm,@initialdate))=5 select @month='May'
if (select datepart(mm,@initialdate))=6 select @month='June'
if (select datepart(mm,@initialdate))=7 select @month='July'
if (select datepart(mm,@initialdate))=8 select @month='August'
if (select datepart(mm,@initialdate))=9 select @month='September'
if (select datepart(mm,@initialdate))=10 select @month='October'
if (select datepart(mm,@initialdate))=11 select @month='November'
if (select datepart(mm,@initialdate))=12 select @month='December'
select @year=convert(char(4),datepart(yy,getdate()))

select @convertdate=@day+ " " + @month + " " + @year

select @convertdate  -- This statement just prints result to screen

I'm offline for rest of the day.  I'll be be back tomorrow if you have any questions.

Gustavo Perez BuenrostroCommented:
Hi, everybody.
This query lets you convert a datetime datatype value to cswkk98's date format (dd Month yyyy):

set language us_english

select convert(varchar,datepart(d,getdate()))

Here is the result:

30 March 2000

This is for passky:

set language Suomi

select convert(varchar,datepart(d,getdate()))

Here is the result:

30 maaliskuuta 2000

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

You answer works for SQL 7.0 just fine, but not SQL 6.5.  I don't know which flavor of SQL 'passky' or 'cswkk98' have, so I gave a example that works for both.  But thanks for the 7.0 example, I learned some new features.
gpbuenrostro, we upgraded to version 7.0 just some months ago, so your solution was working with me. Thanks for language information.

formula, your code is also great.


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.