# Thread: SSIS Julian to Gregorian date conversion

1. Registered User
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.

Benjie

2. Registered User
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. Registered User
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. Registered User
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```

5. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
You can ind the documentation for SSIS date functions at DATEADD (SSIS Expression)

In SSIS, you want to use something like:
Code:
```jDate = 112001
,  jDate / 1000, (DT_DBTIMESTAMP)"12/31/1899"))```
-PatP

7. Registered User
Join Date
Sep 2003
Posts
21
Thanks Pat,

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

Benjie

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

This one works, result is 01/01/12

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

------------------------------ 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. Registered User
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. Registered User
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. Registered User
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.

Thanks.

Benjie

11. Registered User
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.

Regards

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

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

Regards,

Benjie

13. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

14. Registered User
Join Date
Sep 2003
Posts
21
Originally Posted by KHTeoh
Hello bga,

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

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.