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

Posted on 2008-11-05
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
    LVL 73

    Accepted Solution

    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 73

    Expert Comment

    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 73

    Expert Comment

    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

    Hi sdstuber,
    You are correct the values are in seconds,  I used the divide by 86400.
    LVL 73

    Expert Comment

    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

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    Via a live example, show how to take different types of Oracle backups using RMAN.

    746 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now