2

I am trying to build an SSIS package to allow conversion of a Oracle Julian Date to a datetime field for a SQL server DB in MS Visual Studio 2013. What tool would I use to convert my current Julian field into a datetime?

Specifically, I need to convert a date such as: 115365 to 12/31/2015,in my DB. I cant seem to figure out how to use a Derived Column to do so.

Geo
  • 336
  • 1
  • 6
  • 20

2 Answers2

0
SELECT DATEADD(YEAR, @JulianDate / 1000 - 1900, @JulianDate % 1000 - 1)

And the other way is 

SELECT  DATENAME(YEAR, GETDATE()) + RIGHT('00' + DATENAME(DAYOFYEAR, GETDATE()), 3)
Bhanu Chandra
  • 408
  • 8
  • 26
  • Are you plugging this into a Derived Column expression editor? I cant seem to pass this when I try to do so. – Geo Nov 23 '15 at 22:44
0

This is what I used to convert to a DB DATE. This is done in a Derived Column:

DATEADD("day",(DT_I4)[COLUMN] % 1000,DATEADD("yyyy",(DT_I4)[COLUMN] / 1000,(DT_DBTIMESTAMP)"12/31/1899"))
Geo
  • 336
  • 1
  • 6
  • 20