Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2003
    Posts
    21

    Unanswered: SSIS Julian to Gregorian date conversion

    Dear All,

    Need help on how to use SSIS to convert julian date to gregorian date. For example, julian 112001 to gregorian 01/01/2012.

    We are currently using SQL 2008 as the database for ERP/JDE tables.

    Thanks in advance.

    Benjie

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I think your date 112001 is not a Julian day.
    Julian day - Wikipedia, the free encyclopedia
    Nor, UNIX time.
    http://en.wikipedia.org/wiki/Unix_time

  3. #3
    Join Date
    Sep 2003
    Posts
    21
    Thanks tonkuma for the information, it seems that julian date is not straight forward. With the julian date format we have (as per JDE), how can we use SSIS to transform it to gregorian, my input table has julian date and want to convert to gregorian into an output table using SSIS.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I guesses that format of your julian date column might be like ...
    Code:
    julian date 112001 to gregorian 01/01/2012
                yyyddd
    
    Where
    yyy: year - 1900
    ddd: day of year.
    If my guess was right, it would be easy to calculate gregorian, like...
    (your_julian_date / 1000 + 1900) years + (your_julian_date % 1000) dayofyear

    SQL expression might be
    Code:
    SELECT DATEADD( dayofyear , julian_date % 1000 - 1
                  , DATEADD(year , julian_date / 1000 , '1900-01-01')
                  ) AS gregorian
    /* or */
    SELECT DATEADD(year , julian_date / 1000 , '1900-01-01')
           + (julian_date % 1000 - 1) AS gregorian
    I don't know about SSIS.

  5. #5
    Join Date
    Sep 2003
    Posts
    21
    Many thanks for the effort tonkuma, the sample you provided works! I'm half way to solve the conversion. I will try to find out how to apply it in SSIS.

    Anybody who can share their ideas on how to apply it in SSIS?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You can ind the documentation for SSIS date functions at DATEADD (SSIS Expression)

    In SSIS, you want to use something like:
    Code:
    jDate = 112001
    DATEADD("Day". jDate % 1000, DATEADD("yyyy"
    ,  jDate / 1000, (DT_DBTIMESTAMP)"12/31/1899"))
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Sep 2003
    Posts
    21
    Thanks Pat,

    Followup question, how to apply the actual "Field Name" to the function.

    Thanks in advance.

    Benjie

    ------------------------------ Test Result

    This one works, result is 01/01/12

    DATEADD("day",112001 % 1000,DATEADD("yyyy",112001 / 1000,(DT_DBTIMESTAMP)"12/31/1899"))

    This one failed, ZQDSI is the actual field of the julian date

    DATEADD("day",ZQDSI % 1000,DATEADD("yyyy",ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))

    ------------------------------ Error Message

    Error at Data Flow Task [Derived Column [3055]]: Attempt to find the input column named "ZQDSI" failed with error code 0xC0010009. The input column specified was not found in the input column collection.

    Error at Data Flow Task [Derived Column [3055]]: Attempt to parse the expression "DATEADD("day",ZQDSI % 1000,DATEADD("yyyy",ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))" failed and returned error code 0xC00470A2. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

    Error at Data Flow Task [Derived Column [3055]]: Cannot parse the expression "DATEADD("day",ZQDSI % 1000,DATEADD("yyyy",ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))". The expression was not valid, or there is an out-of-memory error.

    Error at Data Flow Task [Derived Column [3055]]: The expression "DATEADD("day",ZQDSI % 1000,DATEADD("yyyy",ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))" on "output column "Derived Column 1" (3307)" is not valid.

    Error at Data Flow Task [Derived Column [3055]]: Failed to set property "Expression" on "output column "Derived Column 1" (3307)".

  8. #8
    Join Date
    Jun 2012
    Posts
    2
    Hi BGA,

    May I know is "ZQDSI" actual a table field or it is a variable ?

    Based on the error, it seems to me "ZQDSI" is a variable.

    If it is a variable then you need to substitute "ZQDSI" with @[User::ZQDSI] instead.

    Hope this helps.

  9. #9
    Join Date
    Sep 2003
    Posts
    21
    Hi KHTeoh,

    "ZQDSI" is a table field, it's a numeric field with length of 6, let me try to convert it to character and see the result.

    Thanks.

    Benjie

  10. #10
    Join Date
    Sep 2003
    Posts
    21

    In SSIS how to convert numeric field to character field inside a DATEADD function

    Dear All,

    In SSIS how to convert numeric field to character field inside a DATEADD function, in the example below the field "ZQDSI" is a numeric field. The objective is to convert julian date to gregorian. The example works if field "ZQDSI" is converted to a character.

    DATEADD("day",ZQDSI % 1000,DATEADD("yyyy",ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))

    Thanks.

    Benjie

  11. #11
    Join Date
    Jun 2012
    Posts
    2
    Hello bga,

    You should cast it to integer instead of character. I had tested the following and it works.

    DATEADD("day",(DT_I4)ZQDSI % 1000,DATEADD("yyyy",(DT_I4)ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))

    Regards

  12. #12
    Join Date
    Sep 2003
    Posts
    21
    Hi KHTeoh / Tonkuma / Pat,

    Great! it finally works, a million thanks...

    Regards,

    Benjie

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Thanks for letting us know it was resolved, it never occured to me to check the column data type even though it should have!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Sep 2003
    Posts
    21
    Quote Originally Posted by KHTeoh View Post
    Hello bga,

    You should cast it to integer instead of character. I had tested the following and it works.

    DATEADD("day",(DT_I4)ZQDSI % 1000,DATEADD("yyyy",(DT_I4)ZQDSI / 1000,(DT_DBTIMESTAMP)"12/31/1899"))

    Regards
    Hi KHTeoh,

    I'm getting an error when ever julian date is equal to zero (ZQDSI). How can I test if the value is not equal to zero, I tried with == "0" but failed.

    Thanks in advance.

    Benjie

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •