Object Object instead of date in pl/sql

Question

Below is my schema to create a Employee and Incentives tables. I want to calculate the data difference between both the dates which are stored as a timestamp.

CREATE TABLE Employee
    ("EMPLOYEE_ID" int, "FIRST_NAME" varchar2(9), "LAST_NAME" varchar2(7), "SALARY" int, "JOINING_DATE" timestamp, "DEPARTMENT" varchar2(9))
;

INSERT ALL 
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (1, 'John', 'Abraham', 1000000, '01-JAN-13 12.00.00 AM', 'Banking')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (2, 'Michael', 'Clarke', 800000, '01-JAN-13 12.00.00 AM', 'Insurance')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (3, 'Roy', 'Thomas', 700000, '01-FEB-13 12.00.00 AM', 'Banking')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (4, 'Tom', 'Jose', 600000, '01-FEB-13 12.00.00 AM', 'Insurance')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (5, 'Jerry', 'Pinto', 650000, '01-FEB-13 12.00.00 AM', 'Insurance')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (6, 'Philip', 'Mathew', 750000, '01-JAN-13 12.00.00 AM', 'Services')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (7, 'TestName1', '123', 650000, '01-JAN-13 12.00.00 AM', 'Services')
    INTO Employee ("EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "SALARY", "JOINING_DATE", "DEPARTMENT")
         VALUES (8, 'TestName2', 'Lname%', 600000, '01-FEB-13 12.00.00 AM', 'Insurance')
SELECT * FROM dual
;



CREATE TABLE incentives
    ("EMPLOYEE_REF_ID" int, "INCENTIVE_DATE" timestamp, "INCENTIVE_AMOUNT" int)
;

INSERT ALL 
    INTO incentives ("EMPLOYEE_REF_ID", "INCENTIVE_DATE", "INCENTIVE_AMOUNT")
         VALUES (1, '01-Feb-2013 12:00:00 AM', 5000)
    INTO incentives ("EMPLOYEE_REF_ID", "INCENTIVE_DATE", "INCENTIVE_AMOUNT")
         VALUES (2, '01-Feb-2013 12:00:00 AM', 3000)
    INTO incentives ("EMPLOYEE_REF_ID", "INCENTIVE_DATE", "INCENTIVE_AMOUNT")
         VALUES (3, '01-Feb-2013 12:00:00 AM', 4000)
    INTO incentives ("EMPLOYEE_REF_ID", "INCENTIVE_DATE", "INCENTIVE_AMOUNT")
         VALUES (1, '01-Jan-2013 12:00:00 AM', 4500)
    INTO incentives ("EMPLOYEE_REF_ID", "INCENTIVE_DATE", "INCENTIVE_AMOUNT")
         VALUES (2, '01-Jan-2013 12:00:00 AM', 3500)
SELECT * FROM dual
;

When performing below select I am getting object object instead of difference, is it the right syntax?

Query:

Select FIRST_NAME,INCENTIVE_DATE - JOINING_DATE from
employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Output:

| FIRST_NAME | INCENTIVE_DATE-JOINING_DATE |
|------------|-----------------------------|
|       John |             [object Object] |
|       John |             [object Object] |
|    Michael |             [object Object] |
|    Michael |             [object Object] |
|        Roy |             [object Object] |

EDIT:

Using this online platform sqlfiddle.com/#!4/aa339/15 as oracle 11g R2

The answer is same as given on below website for Question 37, http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&start=2. In case what does object object signify?

What client are you using to access the DB? SQL*Plus? Toad? SQL Developer? ..? — Ben, Sep 26 '14 at 09:28
Using this online platform http://sqlfiddle.com/#!4/aa339/15 as oracle 11g R2 — garg10may, Sep 26 '14 at 09:30
possible duplicate of [Calculating difference between two timestamps in Oracle in milliseconds](http://stackoverflow.com/questions/11617962/calculating-difference-between-two-timestamps-in-oracle-in-milliseconds) — Arion, Sep 26 '14 at 09:31
@Arion but my query is as per below, http://a4academics.com/interview-questions/53-database-and-sql/397-top-100-database-sql-interview-questions-and-answers-examples-queries?showall=&start=2, Ques: 37, so want to know if this is the right syntax or mentioned wrong on website. — garg10may, Sep 26 '14 at 09:36
Your script and query give the expected result (timestamp subtraction results in an interval) in SQL Developer, so no syntax problem. — Bacs, Sep 26 '14 at 09:43

score 1 · Accepted Answer · answered Sep 26 '14 at 09:43

try using TRUNC()

SELECT
        FIRST_NAME
      , TRUNC(INCENTIVE_DATE) - TRUNC(JOINING_DATE)
FROM employee a 
INNER JOIN incentives B ON A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

Subtracting 2 timestamps is painful as it results in a series of different unit values

also see: http://sqlfiddle.com/#!4/134ab/2

Object Object instead of date in pl/sql

1 Answers1