Oracle APEX Fixing Date Format for Calendar Invitation

Question

I am using the date format DD-MM-YYYY HH24:MI (Greek format date-time) I have 'F_ICAL_EVENTS' function :

create or replace FUNCTION F_ICAL_EVENTS (
   p_summary       IN   VARCHAR2,
   p_description   IN   VARCHAR2,
   p_start_date    IN   DATE,
   p_end_date      IN   DATE
)
   RETURN VARCHAR2
AS
   lv_desc      VARCHAR2 (1000);
   lv_summary   VARCHAR2 (500);
   lv_dtstart   VARCHAR2 (100);
   lv_date      VARCHAR2 (100);
   lv_dtend     VARCHAR2 (100);
   l_retval     VARCHAR2 (2000);
   l_lf         CHAR (1)         := CHR (10);
BEGIN
   lv_summary := 'SUMMARY:' || p_summary;
   lv_date :=
         'DTSTAMP:'
      || TO_DATE (CURRENT_TIMESTAMP, 'DD-MM-YYYY')
      || 'T'
      || TO_CHAR (CURRENT_TIMESTAMP, 'HH24:MI:SS');
   lv_dtstart :=
         'DTSTART:'
      || TO_DATE (p_start_date, 'DD-MM-YYYY')
      || 'T'
      || TO_CHAR (p_start_date, 'HH24:MI');
   lv_dtend :=
         'DTEND:'
      || TO_DATE (p_end_date, 'DD-MM-YYYY')
      || 'T'
      || TO_CHAR (p_end_date, 'HH24:MI');
   l_retval :=
         'BEGIN:VCALENDAR
VERSION:2.0
PRODID:-// Oracle Application Express //ENCAL
SCALE:GREGORIAN
BEGIN:VEVENT
'
      || lv_date
      || CHR (10)
      || lv_dtstart
      || CHR (10)
      || lv_dtend
      || CHR (10)
      || lv_summary
      || CHR (10)
      || lv_desc
      || '
SEQUENCE:0
END:VEVENT
END:VCALENDAR';
   DBMS_OUTPUT.put_line (l_retval);
   RETURN l_retval;
END F_ICAL_EVENTS;

that takes a date time value from my page dynamic action like this:

SELECT F_ICAL_EVENTS (p_summary          => v_summary,
                                p_description      => v_descr,
                                p_start_date       => to_date(:P2003_SESSION_DATE, 'DD-MM-YYYY HH24:MI'),
                                p_end_date         => to_date(:P2003_SESSION_STOP, 'DD-MM-YYYY HH24:MI')
                                )
            INTO l_ical_event
            FROM DUAL;

The problem is when I receive the invitation, the date and time is wrong. For example my Start_date is 10/08/2021 16:00 (10th August) and i gets 8 Dec 0009 12:00.
The session.ics attachment i think is correct:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-// Oracle Application Express //GR
SCALE:GREGORIAN
BEGIN:VEVENT
DTSTAMP:16-08-2021T12:11:37
DTSTART:10-08-2021T16:00
DTEND:10-08-2021T17:00
SUMMARY:New Appointment! 
                               
SEQUENCE:0
END:VEVENT
END:VCALENDAR

This is the notification I receive in my Gmail:

user_9097906 · Answer

On Apex pages, all items are strings. Date picker uses format you set for that item, but it is still a string.

If you set the format to DD-MM-YYYY H24:MI, then calling the procedure looks OK because format masks match.

p_start_date       => to_date(:P2003_SESSION_DATE, 'DD-MM-YYYY HH24:MI')

However, if procedure's parameters datatypes are DATE, then this is wrong:

|| TO_DATE (p_start_date, 'DD-MM-YYYY')

That should be TO_CHAR instead, just as you used it for

|| TO_CHAR (p_start_date, 'HH24:MI');

[EDIT]

Apparently, it was about format mask. For completeness, posting it here from a comment:

According to this page (How to Insert Google Calendar Invites in Your Marketing Emails), date/time format mask should be YYYYMMDDTHHMISS.

user_1509264 · Answer

Don't use TO_DATE on a value that is already a DATE or TIMESTAMP. You appear to want TO_CHAR instead:

create or replace FUNCTION F_ICAL_EVENTS (
   p_summary       IN   VARCHAR2,
   p_description   IN   VARCHAR2,
   p_start_date    IN   DATE,
   p_end_date      IN   DATE
)
   RETURN VARCHAR2
AS
   l_retval     VARCHAR2 (2000);
BEGIN
   l_retval   := 'BEGIN:VCALENDAR
VERSION:2.0
PRODID:-// Oracle Application Express //ENCAL
SCALE:GREGORIAN
BEGIN:VEVENT
DTSTAMP:' || TO_CHAR(CURRENT_TIMESTAMP, 'DD-MM-YYYY"T"HH24:MI:SS') || '
DTSTART:' || TO_CHAR(p_start_date, 'DD-MM-YYYY"T"HH24:MI') || '
DTEND:' || TO_CHAR(p_end_date, 'DD-MM-YYYY"T"HH24:MI') || '
SUMMARY:' || p_summary || '
' || p_description || '
SEQUENCE:0
END:VEVENT
END:VCALENDAR';
   DBMS_OUTPUT.put_line (l_retval);
   RETURN l_retval;
END F_ICAL_EVENTS;
/

Also, if :P2003_SESSION_DATE and :P2003_SESSION_STOP are DATE bind variables then you do not need TO_DATE and can just use:

SELECT F_ICAL_EVENTS (
         p_summary     => v_summary,
         p_description => v_descr,
         p_start_date  => :P2003_SESSION_DATE,
         p_end_date    => :P2003_SESSION_STOP,
       )
INTO   l_ical_event
FROM   DUAL;

If the bind variables are strings then you will need TO_DATE and keep your calling query as you had it but could simplify it to remove the SELECT statement and just use a PL/SQL assignment:

l_ical_event := F_ICAL_EVENTS (
                  p_summary     => v_summary,
                  p_description => v_descr,
                  p_start_date  => TO_DATE(:P2003_SESSION_DATE, 'DD-MM-YYYY HH24:MI'),
                  p_end_date    => TO_DATE(:P2003_SESSION_STOP, 'DD-MM-YYYY HH24:MI')
                );

db<>fiddle here

If the external service you are using is expecting dates in ISO8601 format then use YYYY-MM-DD"T"HH24:MI:SS.

create or replace FUNCTION F_ICAL_EVENTS (
   p_summary       IN   VARCHAR2,
   p_description   IN   VARCHAR2,
   p_start_date    IN   DATE,
   p_end_date      IN   DATE
)
   RETURN VARCHAR2
AS
   l_retval     VARCHAR2 (2000);
BEGIN
   l_retval   := 'BEGIN:VCALENDAR
VERSION:2.0
PRODID:-// Oracle Application Express //ENCAL
SCALE:GREGORIAN
BEGIN:VEVENT
DTSTAMP:' || TO_CHAR(CURRENT_TIMESTAMP, 'YYYY-MM-DD"T"HH24:MI:SS') || '
DTSTART:' || TO_CHAR(p_start_date, 'YYYY-MM-DD"T"HH24:MI:SS') || '
DTEND:' || TO_CHAR(p_end_date, 'YYYY-MM-DD"T"HH24:MI:SS') || '
SUMMARY:' || p_summary || '
' || p_description || '
SEQUENCE:0
END:VEVENT
END:VCALENDAR';
   DBMS_OUTPUT.put_line (l_retval);
   RETURN l_retval;
END F_ICAL_EVENTS;
/

Oracle APEX Fixing Date Format for Calendar Invitation

2 Answers