Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!h76g2000cwa.googlegroups.com!not-for-mail
From: "Mark D Powell" <Mark.Powell@eds.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: command to display time in DATE format
Date: 6 Jun 2006 09:23:14 -0700
Organization: http://groups.google.com
Lines: 40
Message-ID: <1149610994.370402.257220@h76g2000cwa.googlegroups.com>
References: <1149603061.349299.173000@c74g2000cwc.googlegroups.com>
   <ts6dnfV0VoogDRjZnZ2dneKdnZydnZ2d@comcast.com>
   <1149610582.500559.287200@j55g2000cwa.googlegroups.com>
NNTP-Posting-Host: 192.85.50.1
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1149611001 22436 127.0.0.1 (6 Jun 2006 16:23:21 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 6 Jun 2006 16:23:21 +0000 (UTC)
In-Reply-To: <1149610582.500559.287200@j55g2000cwa.googlegroups.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: h76g2000cwa.googlegroups.com; posting-host=192.85.50.1;
   posting-account=J7QqBQwAAABTieek3RP_669Gs2iATWzr
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:268640


Mark D Powell wrote:
> Jim Kennedy wrote:
> > <mehaboob@gmail.com> wrote in message
> > news:1149603061.349299.173000@c74g2000cwc.googlegroups.com...
> > > Hi,
> > >
> > > In oracle DB table,I have a  varchar field which stores time in EPOCH
> > > fomat
> > > for eg:11652232 secs
> > >
> > > Is there a command to display the above time in Date format? (something
> > > like DD MM YY)
> > >
> > > Mehaboob
> > >
> >
> > Why store numbers in character fields?  Store numbers in number fields.
> > (myfield/(24*60*60))+to_date('01/01/1970','mm/dd/yyyy'),
> > Jim
>
> Mehaboob, to display a date in any character representation always use
> to_char.  So jjust put a to_char around Jim's calculation:
> to_char( column / (24 * 60 * 60) + to_date('01-01-1970','mm-dd-yyyy'),'
> DD MM YY')
>
> HTH -- Mark D Powell --

PS the thread subject line mentions displaying the time element but the
OP example does not, but the TIME components can be displayed using HH,
MI, and SS.  See the SQL manual for full options.

UT1 > select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as today from dual;

TODAY
-----------------
20060606 12:22:23

HTH -- Mark D Powell --