Solved

How to convert date in dd Mon yyyy?

Posted on 2000-03-30
14
1,022 Views
Last Modified: 2008-03-17
I want to convert date to format dd Mon yyyy such as 14 April 2000. How? The method as simple as possible
0
Comment
Question by:cswkk98
[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
14 Comments
 
LVL 1

Expert Comment

by:vujos
ID: 2669066
Hi,

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 http://support.microsoft.com/support/kb/articles/Q183/0/49.ASP

Or read http://support.microsoft.com/support/kb/articles/Q222/1/78.ASP that will provide you with complete explanation.

Hope this helps.
0
 
LVL 2

Expert Comment

by:formula
ID: 2669129
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.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2669998
formula, great suggestion !

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

Regards,
Paasky

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 2

Expert Comment

by:formula
ID: 2670186
Paasky,

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

Expert Comment

by:paasky
ID: 2670242
formula,

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?

Paasky
0
 
LVL 2

Expert Comment

by:formula
ID: 2670570
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.

0
 
LVL 10

Expert Comment

by:paasky
ID: 2670647
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'?

Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2670650
106 seems to view short month name...
0
 
LVL 2

Expert Comment

by:formula
ID: 2670806
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.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2670862
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 ..."

Paasky
0
 
LVL 2

Expert Comment

by:formula
ID: 2670916
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.






0
 
LVL 4

Accepted Solution

by:
Gustavo Perez Buenrostro earned 200 total points
ID: 2671120
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()))
      +space(1)
      +datename(m,getdate())
      +space(1)
      +convert(varchar,datepart(yy,getdate()))



Here is the result:

--------------------------------------------------------------------------------------------
30 March 2000



This is for passky:

set language Suomi

select convert(varchar,datepart(d,getdate()))
      +space(1)
      +datename(m,getdate())
      +space(1)
      +convert(varchar,datepart(yy,getdate()))



Here is the result:

--------------------------------------------------------------------------------------------
30 maaliskuuta 2000
0
 
LVL 2

Expert Comment

by:formula
ID: 2672893
gpbuenrostro,

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.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2673161
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.

Paasky


0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

695 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