Date Formatting..

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

  • 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

  • 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

  • How about this one:

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

    returns 20010705

    Andy

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

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

  • This was removed by the editor as SPAM

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

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