Need to change Date Format 2

[powahusr]

How can I change the output of the query "SELECT GETDATE()" to do the following:

2002-01-03
to
Third of January, 2002

I can do this with Oracle, but cannot find a way with SQL Server.


Thanks in advance.

 

[RickCole]

I don't think these are the most elegant of solutions but off the top of my head you could write a stored procedure like the following examples.

Example 1 straightforward but the day will display as 1,2,3, etc.

select date =(select datename(d,getdate()) + ' of ') +
(select datename(m,getdate()) + ', ') +
(select convert (varchar(20), year(getdate())))


Example 2 the same as 1 but changes the displayed day from 1 to the First, 2 to the Second etc. You would need to expand this to include all days of the month.

select date = (case when (select datename(d,getdate())) = 1
then 'First'
when (select datename(d,getdate())) = 2
then 'Second'
when (select datename(d,getdate())) = 3
then 'Third'
when (select datename(d,getdate())) = 4
then 'Fourth'
end
+ ' of ') +
(select datename(m,getdate()) + ', ') +
(select convert (varchar(20), year(getdate())))


Rick.

 

[tlbroadbent]

You could create a UDF in SQL 2000 to convert the date in the manner Rick has suggested.

You might also consider a utility table that stores the conversions from numbers to words for the day of the month. Then you could do a simple lookup and avoided long and complicated CASE statements.

tblNumberConversion
(ConvNo int,
ConvShortName varchar(4),
ConvName varchar(20))
[tt]
Values example:
ConvNo ConvShortName ConvName
1 1st First
2 2nd Second
3 3rd Third
.
.
.
31 31st Thirty-first[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[powahusr]

Thanks a lot for your help.