Tuesday, February 10, 2009

Some date related tips to remember

The function INTERVAL DAY TO SECOND already exists from 9i, but I didn't use it that much before.

An Oracle By Example goes into more detail.

In APEX I created a quick example to show you how you can calculate the difference for dates. Behind the Calculate button I call a Process with this logic:

:P28_INTERVAL_DAY_TO_SECOND := CAST ( TO_DATE(:P28_end_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE )
- CAST ( TO_DATE(:P28_start_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE );

If you are looking for recurrent events (repeating intervals) you can use dbms_scheduler. In the early years of HTMLDB I created an APEX app called Agendimi which was basically an "online Outlook" for teachers. I wish I had the dbms_scheduler package to my disposal at that time!

Here's an example (found here, but adapted for APEX) to get the next 7 days (a plsql region in Apex on the same example page as above:
DECLARE
start_date TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date TIMESTAMP;
BEGIN
start_date := CAST ( TO_DATE(:P28_start_date,'DD-MON-YYYY HH24:MI') AS TIMESTAMP WITH TIME ZONE );
htp.p('Next 7 days (only weekdays)');
return_date_after := start_date;
FOR i IN 1..7
LOOP
dbms_scheduler.evaluate_calendar_string(
'FREQ=DAILY;BYHOUR=9;BYMINUTE=0;BYDAY=MON,TUE,WED,THU,FRI', start_date, return_date_after, next_run_date);

htp.p(TO_CHAR(next_run_date,'DD-MON-YYYY HH24:MI Day'));
return_date_after := next_run_date;
END LOOP;
END;

Another nice one FM... I needed a date format like 1.1.2009, so I thought d.m.yyyy, but I couldn't put that into Oracle. Try: select to_char(sysdate,'d.m.yyyy') from dual; You'll get a nice ORA-01821 date format not recognised. So how do you get that format then?

You need to put FM in front of your date format:
select to_char(sysdate,'FMdd.mm.yyyy') from dual;

5 comments:

Anonymous said...

how did u find the FM thing? I never saw that documented anywhere!

Anonymous said...

cool tip about the FM, didn't know that one.

How do you find these things out?! Do you look in source?

Anonymous said...

needed the FM tip!!!!! thank you!!!!!!!!!!!!!!!!!!

how do u find out these things?!?!?!?!?!? genius!!!!

Dimitri Gielis said...

Hello,

I find the things in reading the manual, blogs, talking to others...

Dimitri

Anonymous said...

Great!!!
thanx