CODEWITHSUNDEEP
oracle-databaseoracle-apexoracle-apex-19.2
Arifullah
Arifullah

Reputation: 69

How to specify date format for date data type in oracle apex form

I want to specify a specific format for a date column in Oracle Apex 19.2 form to limit the user to input the data in a specific format. For example, I want this query for a form to be valid:

Select phone_no, call_date, to_char(call_time, 'HH12:MI:SS AM') 
FROM called_no;

Note: call_date and call_time has the same data type which is date. I only have the problem with call_time column.

Upvotes: 0

Views: 1410

Answers (1)

Littlefoot
Littlefoot

Reputation: 143003

Well, format mask for call_time item would be as you said: hh12:mi:ss am.

If you didn't already do that, do it. If you did, what problems did you have?

However, from my point of view, you're doing it wrong - you should use only one column: call_date whose datatype is date and use it in Apex "as is". It (the item) would accept full date AND time in desired format, e.g. dd.mm.yyyy hh12:mi:ss am.

Because, if you use two columns/items, you'll hit a problem that call_time column's date component will be wrong. Have a look:

SQL> alter session set nls_date_Format = 'dd.mm.yyyy hh12:mi:ss am';

Session altered.

SQL> select sysdate,
  2         to_date('05:33:12', 'hh12:mi:ss pm') right_now
  3  from dual;

SYSDATE                RIGHT_NOW
---------------------- ----------------------
22.12.2021 05:34:19 PM 01.12.2021 05:33:12 AM
-----                  -----
today                  see this? Date component is truncated to 1st of current month

Illustration:

enter image description here

Upvotes: 1

Related Questions