| DATE/TIME Format issue [message #250285] |
Mon, 09 July 2007 08:34  |
bbaz
Messages: 138
Registered: April 2007
|
Senior Member |
|
|
HI Guys,
I have a bit confusing case that I wanted to share with you. I am trying to format my DATE/TIME before inserting into the DB, but I am NOT getting the Proper Time???
When I run the statement in Toad, I get the right result:
Select TO_date(to_char(SYSDATE,'YYYY')||'-'||'02-05 13:19'||':00','YYYY-DD-MM HH24:MI:SS') as newDATE from DUAL;
Result
5/2/2007 1:19:00 PM
The issue is here:
The have a field "ACTUAL_START_DT" of data type 'DATETIME' and Format Mask 'DD-MM hh24:mi'.
When I try to use the same conversion in my form before Updating my record I get the WRONG time (5/2/2007 7:00:00 AM) "The Date is Formatted fine but the TIME IS INCORRECT???", code below:
Update <table_name>
SET
MVT_ACT_START_DT = TO_DATE(to_char(SYSDATE,'YYYY')||'-'||:MVT_MAINTAIN.ACTUAL_START_DT||':00','YYYY-DD-MM HH24:MI:SS'),
There is no way that I am changing the Time before Inserting as I am Displaying the Field Content just after I Trigger 'WHEN-BUTTON-PRESSED'
What am I missing here?
Thanks
|
|
|
|
|
|
 Re: DATE/TIME Format issue [message #251540 is a reply to message #251531] |
Sat, 14 July 2007 08:46  |
bbaz
Messages: 138
Registered: April 2007
|
Senior Member |
|
|
I did the following changes and now the Field is working properly:
1) Changed the Datetime Field Format mask to: DD-MM-YY HH24:MI
2) Now when I try to display the Field Content
Message('ACT start: ' ||to_char(<:blockname.fieldname>,'MM/DD/YYYY HH24:MI:SS'));
The Date is displayed Properly WITH THE TIME (so Time is not missing now)
3) when I Insert/update the corresponding Database Column, I just assign DIRECTLY with no conversion
UPDATE <tablename>
SET myDBcolumn = <:blockname.fieldname>,
HOPE THIS HELPS OTHERS WHO MIGHT FACE SUCH PROBLEMS
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
