Post reply

how to convert datetime into '25th January' format

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

  • January 24, 2012 at 3:11 am

    #1437547

    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)

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • January 24, 2012 at 3:22 am

    #1437556

    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/

  • January 24, 2012 at 3:54 am

    #1437568

    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]

  • January 24, 2012 at 4:55 am

    #1437580

    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.

  • January 25, 2012 at 5:51 am

    #1438181

    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