Post reply

Date format issue in SSIS

  • August 16, 2015 at 9:53 pm

    #299776

    Hi ,

    In my package source is oracle.

    In my source table one numeric column is there which contain the date .Column names are ITMCREDT and ITMENDDT.It contain the date in 20150101 format.

    I want to convert the above date into 2015-01-01 format and load into SQL table which have datetime datatype of above both columns.

    When I am using below code I am getting error

    (DT_STR,4,1252)DATEPART( "yyyy" , ITMCREDT ) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "mm" , ITMCREDT ), 2) +

    RIGHT("0" + (DT_STR,4,1252)DATEPART( "dd" , ITMCREDT ), 2)

    Kindly see the attached screen shot for your reference

    Regards,

    Vipin Jha

  • August 17, 2015 at 7:14 am

    #1820422

    The problem is that your date is not really a date. Is a numeric value that will be implicitly converted to a date to extract the year, month and day. This value won't be the same as the one you expect. To format as you wish, you can simply use SUBSTRINGS.

    SUBSTRING( (DT_STR,10,1252)ITMCREDT, 1, 4) + "-" + SUBSTRING( (DT_STR,10,1252)ITMCREDT, 5, 2) + "-" + SUBSTRING( (DT_STR,10,1252)ITMCREDT, 7, 2)

    The best option will be to always manage dates as dates instead of dealing with strings and numeric values.

    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

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

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