Intercepting Timestamp format before an insert or update

Scriptcase 9 General
#1

Hi All,

Scriptcase is trying to write a timestamp with the following format: YYYY-MM-DD HH:MM:SS:xxxxxx
I need the colon after the “SS” to be a period.
As of right now it is failing on the update.
Where do I go or what do I do to alter this?
I have looked in the options and settings. The regional settings only go down to the second and don’t appear to offer the formatting options I need.

Thanks.

#2

It’s a bit hard to answer as I don’t know how you have setup your application, but dates are always trouble :wink: If you use the default setup then the fields containing dates have properties where you can set the desired mask for your database. If that’s MySQL then that would be odd as it accepts : as well as . (at least it did, never checked recently). If you have set a current timestamp in ‘edit’ then you have to change this to no default and use the onbeforeupdate/onbeforeinsert event(s) to intercept and change the format of your field. But actually I never have heard issues like these.

#3

I think you can not play with a datetime or timestamp field. But I’m not sure.

I do not know the concept of how your form works. But if I needed something like that, I would do this:

I create 2 fields:
“created” and “period”

create AjaxEvent on filed " created"
and automatically fill the field “period”

I attach pictures …

I hope you can help with this.

[ATTACH=CONFIG]n83473[/ATTACH] [ATTACH=CONFIG]n83472[/ATTACH] [ATTACH=CONFIG]n83474[/ATTACH]

screen.jpg

database.jpg

on_click.jpg

#4

Hi Albert and rik,

Thanks for your replies!
I apologize for not being clearer: I am using PostgreSQL 9.6

Albert you said: “If you use the default setup then the fields containing dates have properties where you can set the desired mask for your database.”
By that do you mean in : Form Settings => Fields?
My columns are for audit purposes so they are usually not for showing the end user thus they are not in there. I looked in the “Edit Database Connection” and while there are some database specific settings there isn’t one for timestamps. Is there someplace else I can set that?

rik ~ that is interesting. I may go that route, thanks!

As a last resort I could put a trigger on the table to control those fields. I am just trying to keep as much in the code base as possible. Plus I want to learn what my boundaries are in Scriptcase. Is there a best practice for such columns in Scriptcase?

Thanks.