CODEWITHSUNDEEP
sqloracle-database
Abby D
Abby D

Reputation: 39

rounding off oracle timestamp to integer

INSERT INTO Calculations(TOTAL_DAYS_NUMBER) 
select DTE_CHARGE - (
   select min(TIME_REGISTERED)
   from (
      select TIME_REGISTERED from SHIFTING_RECORDS where id=5
      UNION ALL
      select TIME_REGISTERED from MEAL_RECORDS where id=5
      UNION ALL
      SELECT TIME_REGISTERED FROM REGISTRATION WHERE id=5
      )
   ) 
from REGISTRATION
where id=5;

It gives me output 19 3:28:47.812000000 which is not possible for me to insert in a column having integer datatype, I am interested only in getting those first two digits i.e 19. How Shall I?

Upvotes: 0

Views: 143

Answers (1)

mvp
mvp

Reputation: 116068

Use extract(DAY FROM ...):

INSERT INTO Calculations (TOTAL_DAYS_NUMBER)
SELECT extract(DAY FROM DTE_CHARGE - (
    SELECT min(TIME_REGISTERED) FROM (
        SELECT TIME_REGISTERED
        FROM SHIFTING_RECORDS
        WHERE id = 5

        UNION ALL

        SELECT TIME_REGISTERED
        FROM MEAL_RECORDS
        WHERE id = 5

        UNION ALL

        SELECT TIME_REGISTERED
        FROM REGISTRATION
        WHERE id = 5
    )
))
FROM REGISTRATION
WHERE id = 5;

Upvotes: 1

Related Questions