Passing date value from apex in plsql procedure

Question

I have a problem in Apex with the date format. In my database a date is formatted as '29-05-1994' (DD-MM-YYYY), the same format is being displayed at my apex-page, however not in my session-state (DD-MON-YY). With a procedure I try to calculate someone's age. The result is -79.61. That's not correct, it has something to do with the format. When I change my format to DD-MON-YYYY in the procedure I will get: 2000 something. Still not correct. Does anyone know how to solve this format-problem?

Here is part of my procedure:

> v_datum date := to_date(V('P7_GEBOORTEDATUM'), 'DD-MON-YY');
> v_leeftijd number(10,2);
> 
> -- check my age (in years) 
> v_leeftijd := (sysdate - v_datum)/365;

Answer 1

Date format is a preference in Oracle. That is, you can define a server format (default) that will be overridden by any client-side preference. i.e. server can be set for mm/dd/yy --> application server can be set to use dd-mm-rr (which takes preference) --> client can be set for dd.mm.yyyy (which takes preference)

It's always best to treat dates as strings and convert them back and forth and not assume date formats.

Never use YY if values are under and over the turn of the century. i.e. below 2000 and above. Never use RR if values are under 1950 and over.

It's always best to use YYYY and be guaranteed to have the correct year.

This way you can call to_date(v('v7_geboortedatum'),'dd.mm.yyyy') and be sure that you are working with the correct date.

round ( (sysdate - to_date(v('v7_geboortedatum'),'dd.mm.yyyy')) / 365, 2)