how to convert datetime into '25th January' format

Question

Post reply

how to convert datetime into '25th January' format

Ganeshkumar005

Ten Centuries

Points: 1140
More actions
January 24, 2012 at 2:59 am

#391385

How to convert date '1978-01-25 00:00:00.000' into '25th January' format? The 'th' or 'rd' can be skipped, if difficult. Please help.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246944 More actions · Answer 1

There's probably a faster way, but this works I think:

select convert(varchar(11), cast('1978-01-25 00:00:00.000' as datetime), 13)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

ganeshkumar005 (1/24/2012)
How to convert date '1978-01-25 00:00:00.000' into '25th January' format? The 'th' or 'rd' can be skipped, if difficult. Please help.

You should do this in the presentation layer, not the database.

That being said, you can do it like this: -

BEGIN TRAN

CREATE TABLE #dates ([DATE] DATETIME)

INSERT INTO #dates

SELECT '1978-01-25 00:00:00.000'

UNION ALL

SELECT TOP 50

RAND(CHECKSUM(NEWID())) * 30000 + CAST('1945' AS DATETIME)

FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3

SELECT

CONVERT(VARCHAR(2),DATEPART(DAY,[DATE])) + CASE WHEN DATEPART(DAY,[DATE]) IN (11,12,13)

THEN 'th '

WHEN RIGHT(DATEPART(DAY,[DATE]),1) = 1

THEN 'st '

WHEN RIGHT(DATEPART(DAY,[DATE]),1) = 2

THEN 'nd '

WHEN RIGHT(DATEPART(DAY,[DATE]),1) = 3

THEN 'rd '

ELSE 'th ' END + DATENAME(MONTH,[DATE]),

[DATE]

FROM #dates

ROLLBACK

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

pavanpala.vja Mr or Mrs. 500 Points: 577 More actions · Answer 3

Hey Hi Ganesh...

Please Try the Below Mentioned query..

syntax :

DECLARE @BeginningStay DATETIME

SET @BeginningStay='1978-01-25 00:00:00.000'

SELECT + DATENAME(d, @BeginningStay) +

CASE

WHEN DATENAME(d, @BeginningStay) IN ('1', '21', '31') THEN 'st'

WHEN DATENAME(d, @BeginningStay) IN ('2', '22') THEN 'nd'

WHEN DATENAME(d, @BeginningStay) IN ('3', '23') THEN 'rd'

ELSE 'th'

END

+ ' ' + DATENAME(m, @BeginningStay)

+'' + DATENAME(yy, @BeginningStay)

Pavan Kumar. Pala[font="Verdana"][/font]

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 4

All replies are excelllent. Thanks for your help. Looks like latest one by Pawan will be easy to implement. If not, may go with 1st. Thanks again to all of you.

Regards,

Ganesh.

Ganeshkumar005 Ten Centuries Points: 1140 More actions · Answer 5

Thanks Cadavre. Finally used your script as this was meeting my requirement exactly. Year wasnt required, only day and month.