Post reply

converting into standard date format

  • September 2, 2011 at 4:26 am

    #244112

    hi guys

    i need to convert date presenting in this format 110815 but want to convert it into standard date format.

    any help would be appreciated.

  • September 2, 2011 at 7:44 am

    #1376435

    If your 6 digit (2 year ISO date) is actually an Integer, then the following will do...

    SELECT CAST(CAST(110815 AS CHAR(6)) AS DATETIME);

    If it's already character based, then the following will do...

    SELECT CAST('110815' AS DATETIME);

    Both convert the formatted date to a real DATETIME datatype and that brings me to the next subject. Even though Microsoft saw fit to store formatted dates and times in the scheduled job logs in MSDB, actually storing formatted dates and times in anything other than a temporary reporting output table is one of the worst practices there is in SQL Server or any other RDBMS for that matter.

    I'll also add that it's a best practice to let the GUI or reporting tool (if you have one) format the dates for you. If you absolutely must format dates (for output only!), then please look in Books Online (the Help system that comes with SQL Server) under the keyword "CONVERT" for a wealth of infomation on how to format dates and other things.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 2, 2011 at 7:59 am

    #1376445

    thanks for your reply. much appreciated!

  • September 2, 2011 at 2:25 pm

    #1376689

    You bet... thank you for the feedback.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

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