Converting long value column into date using sql query itself. Oracle, 9i

Posted on 2008-11-05
Medium Priority
Last Modified: 2013-12-07
In oracle db I have a column with integer data type, the actual content is milliseconds of date. I required a sql query which converts those milliseconds value into date value. For example, the value in the column is 1225825655, and required value should be in the form of 'mm/dd/yyyy'. I going to use this query in the iReport, so the modification is required in the query itself.
Question by:jayaprakashkg
  • 4
LVL 74

Accepted Solution

sdstuber earned 375 total points
ID: 22892701
what are the milliseconds in reference to?  They must be a counter from some point in time

Assuming the standard "epoch" of 1970-01-01 00:00:00

TO_CHAR(TO_DATE('1970-01-01', 'yyyy-mm-dd') + 1225825655 / 86400000, 'mm/dd/yyyy')

If Jan 1, 1970 is not your base, then substitute whatever date is your base time you count from.

LVL 74

Expert Comment

ID: 22892707
are you sure your counter is in milliseconds?

1225825655  would only be January 15, 1970

if you counter is in seconds then 1225825655  would be November 4, 2008.

again, assuming Jan 1, 1970 as your base date
LVL 74

Expert Comment

ID: 22892711
so, if your counter is actually seconds rather than milliseconds then use

TO_CHAR(TO_DATE('1970-01-01', 'yyyy-mm-dd') + 1225825655 / 86400, 'mm/dd/yyyy')

Author Comment

ID: 22892866
Hi sdstuber,
You are correct the values are in seconds,  I used the divide by 86400.
LVL 74

Expert Comment

ID: 22894347
Why the penalty B?  If you needed further help you only needed to ask.

Please don't assign penalties without giving the volunteers (not just me) a chance to complete your answer.

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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 video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

616 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