Post reply

To change datetype varchar(14)

  • November 29, 2011 at 11:42 am

    #1414273

    The wrinkle is that "20111107070115" won't CAST to datetime as is. He has to first STUFF a space before the time portion and some colons between the HH:MM:SS.

    Dang. You're right. There really is no easy way to do it. You could write a T-SQL function to do this. A CLR function, if practical, would probably be better. Whatever you do, though, it is going to be ugly, ugly, ugly. Yuck.

    If you are going to write a function to do this, then you might as well do it right and add data validation to it. At that point, though, you are investing some development effort - and to what purpose? To create a poor man's version of the DATETIME data type.

  • December 4, 2011 at 9:50 am

    #1416421

    David Moutray (11/29/2011)

    A CLR function, if practical, would probably be better.

    Heh... "better" how? I don't believe it will be any faster than STUFF. Of course, my opinion isn't worth a hoot. If someone (can't be me... I can't have CLR enabled on the machines I use) would like to do a test, I'd be happy to provide the T-SQL side of the code and the code to build a million row test bed.

    --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)

Viewing 2 posts - 16 through 17 (of 17 total)

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