Oracle: date & time- putting two columns together

Posted on 2012-09-04
Last Modified: 2012-09-04
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!
Question by:maheflin25
    LVL 76

    Accepted Solution

    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')
    LVL 37

    Expert Comment

    by:Gerwin Jansen
    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)

    Author Comment

    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') ;
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    >>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.

    Author Closing Comment

    Excellent folks out there to bail me out.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now