how to convert datetime into '25th January' format

  • 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.

  • 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

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • 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/

  • 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]
  • 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.

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

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

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