Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2915
  • Last Modified:

ORACLE date conversion

I have an oracle 9i database.

I have a date stored in decimal format (seconds from epoch).  I am trying to convert it to a date I can compare to (month, day, year, hour, min, sec)using SQL syntax.

Sample syntax would be greatly appreciated.

Thank you.
0
WW2
Asked:
WW2
1 Solution
 
BasharCommented:
Hi,
I don't know what "epoch" means, but I can help you with some example.

suppose the starting date is 1/1/2002 00:00:00.
You want to convert the following number (which represents seconds from the starting date): 3209475.
You need to convert it to days by using this arithmetic:
(3209475/60/60/24), then you can add the resulting number to the date we have (1/1/2002 00:00:00) by issuing the following SQL statement:
select to_date('1/1/2002 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + (3209475 / 60 / 60 / 24)
from dual

this will give you the new date that represents the number of seconds from the starting date.

I hope this is what you're looking for.

Regards,
Bashar.
0
 
rajeXshCommented:
Cant take credit for this link

http://www.orafaq.com/msgboard/unix/messages/438.htm

It just reiterates what Bashar has posted

-- rajeXsh

0
 
patelgokulCommented:
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
accept bashar's comment as an answer
Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now