| using SYSDATE does not store time [message #161044] |
Wed, 01 March 2006 10:38  |
corus
Messages: 12
Registered: July 2005
Location: Process Control
|
Junior Member |
|
|
Hi there,
After having read many previous posts on the forum regarding the usage of Date formats, I still haven't managed to find a reasonable answer to my problem.
I am basically trying to insert dates (date+time, using sysdate) into a table column of type 'DATE'. I am using Oracle Forms Developer 6i to do this. The database I am using is Oracle 10G 10.2.0.1.0.
I would like to get the following date format when doing this:
'DD-MON-RR HH24:MI:SS'
I have tried many different things to get both date + time stored together in the table column, but with no luck. For some strange reason, when I use sysdate to insert today's date into another 'DATE' column, it would work fine. It seems as if sysdate is not reliable to always get both date and time into a field, from inside Forms.
I have tried the following queries, but with no luck:
1) select sysdate into :date_field from sys.dual;
2) :date_field := sysdate
3) :date_field := to_date(sysdate,'DD-MON-RR HH24:MI:SS')
4) select to_date(sysdate,'DD-MON-RR HH24:MI:SS') into :date_field from sys.dual;
Using these queries would only store the date in format 'DD-MON-RR' or 'DD-MON-RR 00:00:00'
In forms, the :date_field is of type Date and I have included 'DD-MON-RR HH24:MI:SS'as its format mask.
I have also tried altering the session to set NLS_DATE_FORMAT to 'DD-MM'YYYY HH24:MI:SS' to kind of force it to show both date and time, but this syntax is not accepted in Forms. Would it be possible to change the DATE_FORMAT settings for the actual table NLS_DATABASE_PARAMETERS?
I will be glad to provide more information if needed.
Any suggestions would be greatly appreciated,
Regards and Thanks,
Michael
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
