Re: Converting '0000' to midnight in to_date.

There is a time, but you are inadvertantly asking oracle to convert a date type to a character type in order to display it.

use "SELECT TO_CHAR (datt, 'YYYYMMDD HH24MI')" to do an explicit type conversion.

(Incidentally, on the Y2K assessment I did in 1999, about 95% of the problems identified were due to implicit type conversions rather than explicit ones).

-

Regards,

ETA s_m_cc_at_excite.com (Steve M) wrote in message news:<ffdd71df.0211271010.2bdbd6b4_at_posting.google.com>...
> Thanks, I tried that but it doesn't cure my problem.
>
> When I do
> select to_date('20021125 2359','YYYYMMDD HH24MI') from dual;
>
> I do get the time when using TOAD, but not SQLPlus - so the behaviour
> of seeing the time when SELECTing appears to be tool dependent.
>
> Try this:
>
> create table test_t
> (datt date);
>
> insert into test_t (datt) values (to_date('20021125 2359','YYYYMMDD
> HH24MI'));
> insert into test_t (datt) values (to_date('20021125 0000','YYYYMMDD
> HH24MI'));
> insert into test_t (datt) values (to_date('20021125 0001','YYYYMMDD
> HH24MI'));
>
> select * from test_t
>
> DATT
> 11/25/2002 11:59:00 PM
> 11/25/2002
> 11/25/2002 12:01:00 AM
>
> Why is there no time in the second row? (Such as 12:00:00 AM)
>
> Thanks,
> Steve McC
>
>
> "Steve M" <steve.mcdaniels_at_vuinteractive.com> wrote in message news:<as0m77$7uo$1_at_spiney.sierra.com>...
> > select, by default, will not show the time portion. You need to add
> > a conversion to show what you want.
> >
> > SQL> select to_date('20021125 2359','YYYYMMDD HH24MI') from dual;
> >
> > TO_DATE('
> > ---------
> > 25-NOV-02
> >
> > SQL> select to_char(to_date('20021125 2359','YYYYMMDD HH24MI'),'MM/DD/YYYY
> > HH24:MIAM') from dual;
> >
> > TO_CHAR(TO_DATE('2
> > ------------------
> > 11/25/2002 23:59PM
> >
> > "Steve M" <s_m_cc_at_excite.com> wrote in message
> > news:ffdd71df.0211261052.1a43f2d4_at_posting.google.com...
> > > I'm creating a view for data from a legacy system that has the date
> > > and time in separate fields. Date is char in format YYYYMMDD and time
> > > is char in HHMM (24 hour). I'm trying to combine them into a single
> > > date column. All is OK except when the time is null or contains only a
> > > '0', then only the date appears, when I would like the midnight time
> > > also.
> > > This is how I'm combining (from a function):
> > > select to_date(p_dt || ' ' || lpad(nvl(p_tm,'0000'),4,'0'), 'YYYYMMDD
> > > HH24MI')
> > >
> > > When I remove the to_date function, I get the value I expect to be
> > > passed (to the to_date function), for example if p_dt = '20001215' and
> > > p_tm = '0', I get '20001215 0000'. When to_date processes this I get
> > > '12/15/2000' when what I want/expect is '12/15/2000 12:00:00 AM'.
> > >
> > > How can I get the time to show as expected?
> > >
> > > (I have considered conversion to '12:01:00 AM', but only as a last
> > > resort.)
> > >
> > >
> > > TIA
> > >
> > > Steve McC
Received on Tue Dec 03 2002 - 14:56:29 CET