1

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.

MT0
  • 143,790
  • 11
  • 59
  • 117
Will
  • 228
  • 1
  • 2
  • 15
  • "I am trying to create a number of stored procedures in SQL" ... you mean in SQL Server? If so, please update your question and tag to reference SQL Server, not just SQL (which the rest of us take to mean the language!) – Boneist May 18 '17 at 11:06

3 Answers3

0

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Oracle Setup:

CREATE TABLE your_table ( datecol ) AS
  SELECT 1483527061 FROM DUAL;

Query:

SELECT datecol,
       DATE '1970-01-01' + NUMTODSINTERVAL( datecol, 'SECOND' ) AS datetime
FROM   your_table

Output:

DATECOL    DATETIME
---------- -------------------
1483527061 2017-01-04 10:51:01

(If you need to handle leap seconds then you can create a package to handle this.)

Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
0

Actually Oracle stores date / datetime / timestamp values in milliseconds. So to get Oracle representation, multiply Unix timestamp by 1000.

Accordingly, to convert Oracle date / datetime / timestamp value to normal date/time, divide it by 1000 and convert resulting Unix timestamp.

Denis Shelemekh
  • 101
  • 1
  • 6