Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ORACLE date conversion

Posted on 2002-07-16
3
Medium Priority
?
2,914 Views
Last Modified: 2006-11-17
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
Comment
Question by:WW2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 1

Accepted Solution

by:
Bashar earned 200 total points
ID: 7158383
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
 
LVL 2

Expert Comment

by:rajeXsh
ID: 7159407
Cant take credit for this link

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

It just reiterates what Bashar has posted

-- rajeXsh

0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9392263
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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question