Date Formatting..

  • ckempste

    SSCoach

    Points: 17983

    Hi all

    Im trying to get an equivalent function to the oracle TO_CHAR, for example:

    SELECT TO_CHAR(sysdate, 'YYYYMMDD')

    will give me

    20010705

    The datepart function in SQLServer doesnt pad the Month and Day portions, spo this gives me:

    select cast(datepart(yyyy, getdate()) as varchar) + '_' + cast(datepart(mm, getdate()) as varchar) + '_' + cast(datepart(dd, getdate()) as varchar)

    2001_7_5 which is ugly..

    Any easy way out rather than writing up some case statement? perhaps padding based on the len of the returned value (all too much work 😉 )

    Cheers

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • Andy Warren

    SSC Guru

    Points: 119676

    Take a look at the convert statement. Using a style of 121 will give you yyyy-dd-dd, just replace out the dashes and you're there.

    Andy

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 717939

    This solution from liya_kats is probably the best one. If this is SQL Server 2000, you could convert this to a user-defined function calle to_char and call it anywhere you need to. This would make porting code from Oracle easier. The call would have to include the owner (dbo.to_char) in it.

    Steve Jones

    steve@dkranch.net

  • Andy Warren

    SSC Guru

    Points: 119676

    How about this one:

    select convert(varchar(20), getdate(), 112)

    returns 20010705

    Andy

  • ckempste

    SSCoach

    Points: 17983

    Thanks Guys, clean and simple solution.

    Best Regards

    Chris


    Chris Kempster
    www.chriskempster.com
    Author of "SQL Server Backup, Recovery & Troubleshooting"
    Author of "SQL Server 2k for the Oracle DBA"

  • don1941

    SSCarpal Tunnel

    Points: 4183

    quote:


    This solution from liya_kats is probably the best one. If this is SQL Server 2000, you could convert this to a user-defined function calle to_char and call it anywhere you need to. This would make porting code from Oracle easier. The call would have to include the owner (dbo.to_char) in it.

    Steve Jones

    steve@dkranch.net


    Plus 1 vote for this approach. UDFs are the best new feature of SS2K after indexed views. Amazing that Access has had them for some time and the "Premier" data base is just now getting around to it.

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

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