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.)
