• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • Last Modified:

Oracle: date & time- putting two columns together

Hi, we have a table with two columns/data types :

maintenance_start_date - DATE
maintenance_start_time - VARCHAR2

How do I put those two columns together as a full date & time so we can publish the maintenance based on date & time.

Thanks in advance!
  • 2
  • 2
2 Solutions
slightwv (䄆 Netminder) Commented:
What is the format of the start_time column?

Are you sure the date column doesn't already have the correct time?

select to_char(maintenance_start_date,'MM/DD/YYYY HH24:MI:SS') from table_name;

If not, just do the to_char on the date, concatenate the time and do a to_date:

assuming the varchar2 time is in HH24:MI:SS format:

to_date(to_char(maintenance_start_date,'MM/DD/YYYY') || maintenance_start_time,'MM/DD/YYYY HH24:MI:SS')
Gerwin Jansen, EE MVETopic Advisor Commented:
As slightwv is suggesting, just adding a 'name' would look like this:

select to_char(maintenance_start_date,'MM/DD/YYYY') || " -  " || maintenance_start_time as maintenance_start_date_time
from your_table;

Open in new window

(adding a separator and not formatting the start_time because already VARCHAR2)
maheflin25Author Commented:
Excellent - You've got it!!

The format for start_time is HH24:MI:SS. The time is selected by user.

Now, my next challenge is to get it to display data based on sysdate (now and 3 days prior):

select * from system_status where to_date(to_char(maintenance_start_date,'MM/DD/YYYY') || maintenance_start_time,'MM/DD/YYYY HH24:MI:SS') between
 to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') and to_char(sysdate+3, 'MM/DD/YYYY HH24:MI:SS') ;
slightwv (䄆 Netminder) Commented:
>>between to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS') and to_char(sysdate+3, 'MM/DD/YYYY HH24:MI:SS') ;

No.  Compare dates with dates.  Do not use to_char on sysdate.

between trunc(sysdate)-3 and trunc(sysdate)

the TRUNC call removes the time portion of sysdate.
maheflin25Author Commented:
Excellent folks out there to bail me out.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now