Skip to Main Content
  • Questions

  • Timestamp validation against current timestamp

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Evans.

Asked: April 01, 2020 - 7:29 am UTC

Last updated: April 03, 2020 - 2:36 am UTC

Version: 19.2

Viewed 1000+ times

You Asked

Hello,
I am a newbie developing an app on APEX 19.2. I have a table with a field START_DATE of data type TIMESTAMP WITH LOCAL TIME ZONE.

I also have a form with this field, for which I have specified Identification Type as Date Picker, and Format Mask DD-MON-YYYY HH24:MI:SS.

With the default validation on this field which checks that the value is of timestamp format, it all works very well; I can input date and time using the date picker and the form submits fine.

I am now trying to add an additional validation to ensure that the START_DATE is a future timestamp. So for this validation, I set the Validation Type as PL/SQL Expression and set the following for the expression:

:P17_START_DATE > CURRENT_TIMESTAMP

For completeness, I have set the Error Message as "#LABEL# must be in the future" and Associated Item as P17_START_DATE (which is how the form item appears). All other settings for the validation are left as default.

If I then try to submit the form, I get an error message saying "Error processing validation."

What am I doing wrong?

and Connor said...

The issue is the format mask.

Your items in APEX are essentially all strings, ie, a browser doesn't really have the idea of a datatype.

So (without a format mask), when you pick a date from a picker in APEX, you are are getting a formatted string into your item, where the format is *known* to APEX.

When your validation fires, whilst it reads like:

:P17_START_DATE > CURRENT_TIMESTAMP

the database is doing this behind the scenes to get the datatypes correctly aligned:

to_timestamp(:P17_START_DATE) > CURRENT_TIMESTAMP

That will work when you are not using a format mask. However, when *you* add your own format mask, that call above might fail because yours might not align with what apex/database is expecting.

So just change you validation to explictly control the formatting, ie

to_timestamp(:P17_START_DATE,'DD-MON-YYYY HH24:MI:SS') > CURRENT_TIMESTAMP

and you should be good to go

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Or use the built-in Substitution string

Alex Nuijten, April 02, 2020 - 8:12 am UTC

Instead of "hard coding" the data time format mask, you can also refer to the APEX built-in substitution string. I think APP_NLS_TIMESTAMP_FORMAT is the one to use in this case.

Docs: https://docs.oracle.com/en/database/oracle/application-express/19.1/htmdb/understanding-substitution-strings.html#GUID-34118202-1B85-4D38-8C27-F819679504DB
Chris Saxon
April 02, 2020 - 10:03 am UTC

Thanks for sharing!

Problem solved!

Evans, April 02, 2020 - 5:37 pm UTC

That solved it, many thanks for that and for the post on an alternative solution.
Connor McDonald
April 03, 2020 - 2:36 am UTC

Glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

APEX

Keep your APEX skills fresh by attending their regular Office Hours sessions.