Re: Storing only the time *NOT* the date --- HOW ???

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Wed, 2 Dec 1998 08:01:54 +0100
Message-ID: <742onb$7q9$1@newton.a2000.nl>

Jørgen Haukland wrote
>How do I update my time_fld to store *only* the time (and
>not the date) ??

An Oracle date column will always hold both date and time. Using trunc(..) you may set the time part to 0:00:00, suggesting that only the date is stored, but in fact the time part is still there. However, you can not set the date to zero, as the year zero never existed, nor did the month zero and the day zero. Futhermore, internally Oracle does not store dates as character strings. When you use

    my_date := to_char ( .. )

then you force Oracle to transform the char back to a date by itself. So, what is executed is actually:

    my_date := to_date ( to_char( .. ) )

This is very dangerous, as it now depends on your default date format (most likely DD-MON-YY) of how this implicit transformation is done. Copy the following to SQL*Plus and see what happens here:

    select to_char( to_char( sysdate

                           , 'DD-MON-YYYY'
                           )
                  , 'DD-MON-YYYY'
                  )

    from dual;

See what I mean? Dates are important, so read your manual!

By the way, your query would not need a sub-query at all. Just

    UPDATE my_table
    SET time_fld = TO_CHAR(time-fld, 'HH24:MI:SS');

would do. A sub-query requires some unique link between the update part and the subquery, like:

    UPDATE my_table A
    SET A.time_fld =

      ( SELECT TO_CHAR(time-fld, 'HH24:MI:SS')
        FROM my_table B
        where A.rowid = B.rowid
      );

However, you can not use it to set a date column, like I explained above. I suggest you store the time as a varchar2 or number column.

Arjan. Received on Wed Dec 02 1998 - 01:01:54 CST