Date conversions: time in varchar field, date in date field. 2

[hallux]

Hello,
What is the best method to combine a time and date into one field of type date? Let me explain.

These are the two fields in the table:
field, datatype, example
the_time, varchar2(5),17:54
the_date, date, 01/01/2004 (note that the time portion is always set to midnight, 00:00:00)

I want to combine the_date + the_time into one field of datatype = date. It would be something like "01/01/2004 17:54:00".

Thank you,
-Hallux

 

[nagornyi]

to_date(the_date||' '||the_time, 'MM/DD/YYYY HH24:MI')

Change the mask if day, not month goes first

 

[SantaMufasa]

Hallux,

Yes, what you want is a very reasonable request. Here, then, is code that should contribute to the resolve your need. (I present it in "wide-screen" mode so you can see all the date functions on one row for clarity's sake. You will need to scroll right/left to view it all.):

*********************************************************************************************************************
col a heading "Time|Component" format a10
col b heading "Original|Date/Time" format a20
col c heading "Consolidated|Date/Time" format a20
select the_time a,to_char(the_date,'yyyy-mm-dd hh24:mi:ss')b from hallux;

Time       Original
Component  Date/Time
---------- -------------------
17:54      2004-01-01 00:00:00

1 row selected.

select to_char(to_date(to_char(the_date,'yyyy-mm-dd')||' '||the_time,'yyyy-mm-dd hh24:mi'),'yyyy-mm-dd hh24:mi:ss')c
from hallux;

Consolidated
Date/Time
-------------------
2004-01-01 17:54:00

1 row selected.

update hallux set dt_time_combination = to_date(to_char(the_date,'yyyy-mm-dd')||' '||the_time,'yyyy-mm-dd hh24:mi');

1 row updated.

select	 the_time a
	,to_char(the_date,'yyyy-mm-dd hh24:mi:ss')b
	,to_char(Dt_time_combination,'yyyy-mm-dd hh24:mi:ss')c
from hallux;

Time       Original             Consolidated
Component  Date/Time            Date/Time
---------- -------------------- --------------------
17:54      2004-01-01 00:00:00  2004-01-01 17:54:00

1 row selected.

Let us know if this depicts a resolution to your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:45 (16Sep04) UTC (aka "GMT" and "Zulu"), 14:45 (16Sep04) Mountain Time)

 

[SantaMufasa]

Nagornyi is obviously a faster "gun-slinger" than I am. Sorry if my post looks like a duplicate of Nagornyi's. (I'll be so happy if/when Tek-Tips can advise posters that another poster has submitted a response to the same thread before we submit our [duplicate] posts.)

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:48 (16Sep04) UTC (aka "GMT" and "Zulu"), 14:48 (16Sep04) Mountain Time)

 

[hallux]

Thank you all. It works perfectly.
-Hallux