Post reply

Best way to convert a datetime field into a char(8) '20081117' in dataflow

  • November 17, 2008 at 9:16 am

    #124314

    Hi All,

    I would like to know the most efficient way of adding a new field to the dataset within the pipeline. I need to convert a datetime field into a char(8) e.g. '20081117' so I can perform a look up against a dimension table and grab the surrogate key.

    I know there is probably a few ways to do this but I require the most efficient.

    Thanks

  • November 17, 2008 at 9:24 am

    #899388

    try: convert(char(8), DateField, 112)

    replace DateField with the field that holds the date.


    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • November 17, 2008 at 9:34 pm

    #899671

    Why does the dimension table have dates that have been converted to varchar to begin with?

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

  • November 18, 2008 at 1:36 am

    #899718

    It does have a datetime field but this contains 2008-11-01 00:00:00.000 and so on. So if we have a datetime field with 2008-11-01 12:58:21.000 we cannot use this field to join without first manipulating the data somehow.

  • November 18, 2008 at 1:56 am

    #899721

    Hi,

    You can also use

    SELECT CAST ( '20081117' AS char(8))

  • November 18, 2008 at 5:19 am

    #899809

    aaa (11/18/2008)

    It does have a datetime field but this contains 2008-11-01 00:00:00.000 and so on. So if we have a datetime field with 2008-11-01 12:58:21.000 we cannot use this field to join without first manipulating the data somehow.

    Ah... got it...

    Still, toe-to-toe, conversion to Varchar will be slower for the convert and slower for the join. Since that conversion already puts the kabosh on the use of Index Seeks, you might want to consider joining the date columns based on the number of days since 19000101. If humans don't need to look at it (they shouldn't, it's in a join), then the following formula does such a thing...

    SELECT DATEDIFF(dd,0,yourdatecolumn)

    If humans do need to look at it or the process requires a date output, then then just reconvert the above, like this...

    SELECT DATEADD(dd,DATEDIFF(dd,0,yourdatecolumn),0)

    Another trick to speed up a join on a "calculated date" is to build a calculated to do the time stripping and then index it. Unfortunately, it's a bit of a tradeoff because DATEDIFF is non-deterministic and you end up having to use Alvin Ramard's suggestion of...

    Convert(char(8), yourdatecolumn, 112)

    ... as the forumula in the calculated column so that it CAN be indexed.

    --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 6 posts - 1 through 5 (of 5 total)

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