SQL Developer

Hi,
When you say: -

"When it gets inserted into the table, and I do a to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS'), its truncated to 22-JUN-2006 00:00:00."

how are you inserting the dates into the table?? A PL/SQL process?

You've ruled out using a Trigger, but I'm not sure why. Create a before insert Trigger and then you're guaranteed to record a timestamp on any table changes irrespective of where the data is entered from.

Cheers,

Mike

For such a hings TRIGGERS are not only but BEST solution.

Imagine what happeneed if someone update values from SQLPlus! What then? So if you really do not that these updates are triggered, then disable that trigger and later update it.

P.S.
If you have problem with DATE format then look in topic on that subject and HTMLDB how to deal with dates.

If you want to deal with fractals look into query:

SELECT to_char(<b>systimestamp</b>, 'HH24:MI:SS.<b>FF3</b>') FROM dual;

Hope this helps!

Let me clarify. I fully understand a trigger would solve this, so forgive my stubborness in advance. I have nothing against a trigger per se, but I am used to the Oracle Apps 11i forms where no db triggers are used. I am trying to learn Apex the best way - just by developing. I want to see what the tool is capable of. If there is not a simple answer I will use a trigger. By the way, if it makes a difference, this is using Oracle's sandbox, and not my own database.

Anyway I created a form through the wizard - it is sitting on top of a very small table with 2 date fields (date datatype). Apex created the buttons and form fields. I left them as text so I could verify the values, but I would set them as hidden once I am happy. It set the source as the database columns creation_date and last_update_date respectively.

Ok here's where the actual question comes in. For both of those fields (call them P1_LAST_UPDATE_DATE and P1_CREATION_DATE) I set the default value in their properties to sysdate. Ran the form, it shows up simply as 23-JUN-2006. I figured out if I change the format mask property to DD-MON-YYYY HH24:MI:SS then I can show the full timestamp.

Since Apex created the form, it also does an automatic insert/update process that I didn't write. If I dont use a format mask, it truncates the date and sets it to midnite. If I set the format mask, I get the date picture ends before string error because the format mask is now a character string, and Apex does not know enough to convert that into a date. I then tried a post computation calculation to try to_date on the character string. Again it truncated the date when it put it in the table. When I say truncate I mean its set to midnite instead of 3:02:23.

Maybe the NLS settings are not what I am expecting? Keep in mind this is not my database and its the Oracle provided space. Thanks for all of your help - hopefully I was clear. If need be let me know and I'll post a link to my app. I am new with all of this so thanks in advance for the patience.

Brodie

I think you're almost there - the date is stored with all the elements regardless of the format mask you use. Are you stroring the date in a date column?

Can you share the app so we can see the format mask/field problem ourselves please?

Phil

I'm following this correctly, I think the 'problem' you're running into is how ApEx is handling things.

Since this is all web based, screen items (especially wizard created ones) are ALL text fields, since they all wind up with 'extra' code behind them that's difficult for you to see. These are all HTML form fields, so they all wind up getting the '<input ....> stuff wrapped around them, basically rendering non-text fields (like dates and numbers) into a text field, unless you do a few 'extra' steps like the format mask. ApEx then knows from the presence of the format mask to to also perform a to_number or to_date conversion before actually storing the data in the table.

You will get the same results in your date fileds if you try to add a text string (in the proper format) to your date field, the date is correct, but the time is gone.

Hope this helps.

Bill Ferguson

Phil,

Thanks for the response.

Ok this is weird - I actually didnt catch this until now. When I query using the SQL Commands window in the browser, the date is stored as 23-JUN-06. When I actually do to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS'), it comes out as 23-JUN-0006. I didn't notice the 0006 because I was too busy looking at the time stamp.

I decided to let Apex create the table for me as well, so maybe this is where my troubles are coming from. I notice in the describe that they list the date column length as 7. Which seems weird.

Describe courses

COURSES	COURSE_ID	        Number	  - 	 - 	 - 	1	 - 	 - 	 -
 	COURSE_CATALOG_NUMBER	Varchar2 20	- 	- 	- 	- 	- 	-
 	CREATION_DATE	        Date	 7	- 	- 	- 	nullable	- 	-
 	LAST_UPDATE_DATE	Date	 7	- 	- 	- 	nullable	- 	-
 	CREATED_BY	        Number	- 	 - 	- 	- 	nullable	- 	-
 	LAST_UPDATED_BY	        Number	- 	 - 	- 	- 	nullable	- 	-

Yes I think my issue is just in understanding how Apex is handling dates. I am really green to web stuff, so I figure there is probably a step in between that I am missing.

And again I know I could solve this with triggers, but I would like to understand how and why Apex does some of the things it does.

Why not replace your text item with a calendar item and use the full format mask you require?
Or, when Creating use a different process that inserts sysdate (not the item value) into your created date?

Ok I absolutely apologize for being a complete idiot. I just figured out what was going wrong. The format mask did not match the default value. I had forgotten to add the second to the format mask, so it never matched up.

I will try to give you back the hour of your lives you've spent.

That's okay, no problem.

But, I will take my hour from back when I was still 17 or 18. :^)

I'm working on a pl/sql time machine. So when I get it hooked up to Application Express I'll give you a URL and we'll set something up :)

Cool - please ensure there is an API to allow me to buy backdated lottery tickets and place bets on horse races.