how to use BETWEEN in Julian dates using PLSQL

Question

I am trying to get all record from a speficic date shown below but it seems that the format of the dates are in Julian date (which I cannot change unfortunately)

  order_date BETWEEN TO_DATE('2015/06/04 10:31:32', 'yyyy/mm/dd hh24:mi:ss') AND TO_DATE('2015/06/04 10:31:32', 'yyyy/mm/dd hh24:mi:ss')

I have received the error inconsistent datatypes: expected NUMBER got DATE. I think it is because of the format. I am unable to know how to convert the date above in the sql to a julian date. Thanks!

EDIT: Adding order_date sample date

order_date
1332168537
1332105720
1335616468

Answer 1

select 1 from dual where to_number(1) between sysdate -1 and sysdate;

The same error. Because order_date is number and db expets number in betweene statement.
order_date is unix style date(secound since 1970). To convert try this.

select order_date/24/60/60 + to_date('1970-01-01','yyyy-mm-dd') from your_table;

Answer 2

It seems that your dates are stored as an offset (number) from 00-JAN-1900. That is NOT a Julian date. Julian dates start at 01-JAN-4713 BC. Oracle interprets the Julian origin as 01-JAN-4712 BC, probably having to do with the non-existent year 0.

See this post - https://community.oracle.com/thread/2425218

Anyway, you are adding two numbers, converting it to character and interpreting it as a date with format YYYYDDD. That will only work for a range of 1-365. Any other number will result in error ORA-01848.

What you should do instead, is take your origin as a date (that would be 01-01-1900) and add a number of days (minus 1 since your origin is one off). sql> select to_date('19000101','YYYYMMDD') + datecolumn_which_is_actually_a_number - 1 from dual; But the real WTF is storing a date in a number instead of a date datatype.

and consider:

SQL> select to_char(sysdate,'J') from dual;

TO_CHAR

2456143

SQL> select to_date(2456143,'J') from dual;

TO_DATE(2456143,'J')

03-AUG-2012 00:00:00