Post reply

convert string YYYYMM into date SSIS

  • May 15, 2017 at 7:26 am

    #330346

    Hi
    new to SSIS ,

    I am trying to convert a column "Date" in excel YYYYMM (Currently a string) into a date to go into a SQL server table

    Example 201703

    I have seen many answers to convert YYYYMMDD but not YYYYMM

    I been trying to use a Derived Column but no joy,

    please help

  • May 15, 2017 at 7:32 am

    #1942541

    joanna.seldon - Monday, May 15, 2017 7:26 AM

    Hi
    new to SSIS ,

    I am trying to convert a column "Date" in excel YYYYMM (Currently a string) into a date to go into a SQL server table

    Example 201703

    I have seen many answers to convert YYYYMMDD but not YYYYMM

    I been trying to use a Derived Column but no joy,

    please help

    How do you determine which day of the month you want to use? First? Last? Random?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 15, 2017 at 7:36 am

    #1942543

    Hi

    that is a good point

    I would use the 01 (First) of the month

    please help

  • May 15, 2017 at 7:51 am

    #1942549

    joanna.seldon - Monday, May 15, 2017 7:36 AM

    Hi

    that is a good point

    I would use the 01 (First) of the month please

    The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.

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

  • May 15, 2017 at 8:04 am

    #1942554

    joanna.seldon - Monday, May 15, 2017 7:36 AM

    Hi

    that is a good point

    I would use the 01 (First) of the month

    please help

    Try something like this 
    (DT_DATE) ( left(@DodgyDate,4) + "-" + right(@DodgyDate,2) + "-01")

    Replacing @DodgyDate with your own variable/column name, of course.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 15, 2017 at 8:12 am

    #1942561

    Jeff Moden - Monday, May 15, 2017 7:51 AM

    joanna.seldon - Monday, May 15, 2017 7:36 AM

    Hi

    that is a good point

    I would use the 01 (First) of the month please

    The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.

    And fail miserably because SSIS won't recognize that format (who decided that?).
    You would need to add some delimiters.
    (DT_DATE) ( LEFT(@[User::Month],4) + "/" + RIGHT(@[User::Month],2) +"/01")

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • May 15, 2017 at 9:57 am

    #1942590

    Luis Cazares - Monday, May 15, 2017 8:12 AM

    Jeff Moden - Monday, May 15, 2017 7:51 AM

    joanna.seldon - Monday, May 15, 2017 7:36 AM

    Hi

    that is a good point

    I would use the 01 (First) of the month please

    The typical solution, then, is to concatenate '01' to the end of that string and then do a simple direct conversion to a date/time.

    And fail miserably because SSIS won't recognize that format (who decided that?).
    You would need to add some delimiters.
    (DT_DATE) ( LEFT(@[User::Month],4) + "/" + RIGHT(@[User::Month],2) +"/01")

    Thanks, Luis.  That's a bloody amazing short-coming, IMHO.  I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.

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

  • May 15, 2017 at 10:14 am

    #1942592

    Jeff Moden - Monday, May 15, 2017 9:57 AM

    Thanks, Luis.  That's a bloody amazing short-coming, IMHO.  I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.

    I feel like a challenge: let's see if we can find something about SSIS that you would like 🙂
    Imagine the situation where you have a delimited string. You don't know its length, nor how many elements it may contain. But you do know that you always want to return the fourth item in the string.
    That's a simple one-liner in SSIS:
    token( @[User::Variable],"/",4)
    where @[User::Variable] is the variable containing the delimited string, / is the delimiter and 4 is the occurrence number.
    That beats any T-SQL solution I can think of.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • May 15, 2017 at 6:34 pm

    #1942636

    Phil Parkin - Monday, May 15, 2017 10:14 AM

    Jeff Moden - Monday, May 15, 2017 9:57 AM

    Thanks, Luis.  That's a bloody amazing short-coming, IMHO.  I keep trying to be open minded about SSIS but things like this drive another nail into the list of reasons I don't care for it.

    I feel like a challenge: let's see if we can find something about SSIS that you would like 🙂
    Imagine the situation where you have a delimited string. You don't know its length, nor how many elements it may contain. But you do know that you always want to return the fourth item in the string.
    That's a simple one-liner in SSIS:
    token( @[User::Variable],"/",4)
    where @[User::Variable] is the variable containing the delimited string, / is the delimiter and 4 is the occurrence number.
    That beats any T-SQL solution I can think of.

    Now that's useful.  How's the performance?

    --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 9 posts - 1 through 8 (of 8 total)

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