Re: PLEASE HELP

Steven wrote
>current date in datebase is 01-Mar-98

I doubt the dates are stored like 01-Mar-98. If you use a DATE column in your table, then Oracle always stores the full time stamp. That is: both full date and time. Internally, Oracle does not store it as a character string! But as your
default date mask might be DD-MON-YY you'll only see the 01-MAR-98 part when querying.

When converting a character string to a date, use:

update my_table
set my_date=to_date( '01-JAN-1998', 'DD-MON-YYYY');

It would be too much to completely cover Oracle dates here. Make sure to see your manuals for the CC, MON, Mon, mon, RR and RRRR date formats, and the use of the trunc(..) function. Also check out date calculations, like sysdate + 1) yields tommorrow, same time, or (sysdate + 120/(24*60*60)) yields 120 seconds from now!

Some food for thought:

• create a test table create table test_table(my_date date);
• insert the full time stamp into this table: insert into test_table values (sysdate);
• use the default date mask: select my_date from test_table;
• now, specify a print format: select to_char(my_date, 'DD-Mon-YYYY HH24:MI:SS') from test_table;
• change the default date mask for this session: alter session set nls_date_format = 'YYYYMMDD';
• again, use the (new) default date mask: select my_date from test_table;
• CC is the century -- 20th for 1998! select to_char(my_date, 'DD-MON-CCYY') "Oops!" from test_table;
• use trunc(..) if the time should be zero: select 'Yes' "The same?" from test_table where my_date = sysdate;

select 'Yes' "The same?"
from test_table
where trunc(my_date) = trunc(sysdate);

• never use to_date to convert a date to a date... alter session set nls_date_format = 'DD-MON-YY'; update test_table set my_date = to_date( my_date, 'DD-MON-YYYY');
• Oops! select to_char(my_date, 'DD-MON-YYYY HH24:MI:SS') from test_table;

Regards,
Arjan. Received on Fri Dec 04 1998 - 20:15:04 CET