Displaying dates - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Displaying dates

Author

Topic

malletts
Starting Member

16 Posts

Posted - 2003-03-24 : 05:12:21

Hi all,

I'm trying to display full month in the form of Tuesday 12th March 2003 using the following statement:

select LEFT(DATENAME(dw,interview_date),9)+ ' '+CONVERT(char(12), interview_date, 113) from tblapplicant

This outputs the following format

Friday 12 Dec 2003

I need to display full month i.e December

All help gratefully received
Stephen

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-24 : 08:31:46

HTH..
select DATENAME(WEEKDAY, GETDATE()) + ' ' + DATENAME(DAY, GETDATE())
+ 'th ' + DATENAME(MONTH, GETDATE()) + ' ' + DATENAME(YEAR, GETDATE())

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

malletts
Starting Member

16 Posts

Posted - 2003-03-24 : 11:29:24

hi,

this is fine for one particular date but what about the cases were it is the 1st, 2nd, 3rd etc where you require a different suffix...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 11:35:09

You will need to write a user-defined function that takes a day as an argument and returns a varchar() with the "th" or "rd" or whatever added to it.

something like:

create function DayWithSuffix(@Day int)
returns varchar(5) as
begin
declare @Ret varchar(5);
set @Ret = convert(varchar(2), @Day) +
case @day when in (1,21,31) then 'st'
when in (2,22) then 'nd'
when in (3,23) then 'rd'
else 'th' end
return (@Ret)
end

* warning : above was not tested.. but you get the idea.

Call it like:

select dbo.DayWithSuffix(Day(YourDate))

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources