Converting an Oracle date (stored as a number) in a SQL OPENQUERY

I am trying to create a number of stored procedures in SQL accessing an Oracle database.

All dates in Oracle are stored as a 10 digit number which is causing some confusion.

I have designed some dynamic SQL which allows us to run an OPENQUERY to pick out parameters however what I want to be able to do is use parameters to specify dates.

This is proving tough as we are unable to create views on the server, if anyone has something which they can suggest that would be brilliant.

Examples:

Oracle 10 : 1483527061
SQL Date  : 2017/01/04 10:51:01 000

Please let me know if you need any more information.

Answer

The date/time in Oracle looks like a Unix format. If so, you can do:

select date '1970-01-01' + datecol / (60*24*24)

This will convert it to a date in Oracle.