APEX: convert date item to date

[martindsouza]

APEX items are all in string format. Dates are stored as strings so referencing :p1_my_date is actually a string. That string is based on either the NLS date settings OR the explicit date format.

Provide a function to pass in the page item name and get the real date back. This function will look at which format to use in a to_date function

[NamithaMurali]

CREATE OR REPLACE FUNCTION get_apex_date( p_item_name IN VARCHAR2 ) RETURN DATE IS l_value VARCHAR2(4000); l_format_mask VARCHAR2(200); BEGIN -- Get the runtime value of the item l_value := apex_util.get_session_state(p_item_name);

-- Try to get the Format Mask from APEX metadata
SELECT format_mask
  INTO l_format_mask
  FROM apex_application_page_items
 WHERE application_id = v('APP_ID')
   AND page_id        = v('APP_PAGE_ID')
   AND item_name      = p_item_name;

EXCEPTION WHEN NO_DATA_FOUND THEN -- No explicit format mask → use session NLS l_format_mask := NULL; END;

-- If no explicit mask found, use NLS date format
IF l_format_mask IS NULL THEN
    l_format_mask := (SELECT value FROM nls_session_parameters
                      WHERE parameter = 'NLS_DATE_FORMAT');
END IF;

RETURN TO_DATE(l_value, l_format_mask);

END get_apex_date; /