You can translate the question and the replies:

Converting a Julian date stored in an Oracle DB as a NUMBER to a 'date' data type

Has anyone successfully implemented a function for converting a Julian date stored as a NUMBER in the RDBMS to a 'date' as used by VDP? I wasn't able to find any help. I found some imperative algorithms for doing so, but nothing easily converted to a declarative SQL style. Virtual DataPort 6.0 20170220 com.denodo.vdp.jdbc.Driver 6.0 20170220 In Oracle: ``` select trunc(sysdate) as normal_date, to_char(trunc(sysdate), 'J') as julian_date from dual; ``` ``` 05/09/2017 00:00:00 2457883 ```
09-05-2017 14:39:15 -0400

2 Answers

Hi, I would convert a date to another date format by using the FORMAT_DATE function. For example, if I need to convert a date to format “ddMMyyyy”, then I would execute the following query in VQL shell, ``` SELECT FORMATDATE('ddMMyyyy',cast('2014-02-15' as date)) as value ``` | value | | -------- | | 15022014 | You could also create a custom function for converting date to Julian date format. The custom function is implemented in JAVA and added to Virtual DataPort as a Jar file by using the “File > JAR management” option. I would refer the section “Developing Custom Functions” in [Virtual DataPort Developer Guide]( Hope this helps.
Denodo Team
11-05-2017 05:15:07 -0400
I was able to work around it by created virtual columns in the DB to convert the Julian dates to date data types.
17-05-2017 10:22:56 -0400
You must sign in to add an answer. If you do not have an account, you can register here