Re: Converting '0000' to midnight in to_date.

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 Wed Nov 27 2002 - 19:10:12 CET