Re: Converting '0000' to midnight in to_date.

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('

"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 Nov 26 2002 - 21:40:16 CET