Hi guys,
I’m writing a PL/SQL procedure, one of the parameters is a date.
It is declared as DATE.
When I use Toad to run the procedure, it opens the usual “Set parameters” window and I can enter my date, fully, but the date recorded inside Oracle is a short date, it does not take hours, minutes and seconds.
Any clue anyone ?
Many thanks
JM
Please explain “recorded inside Oracle.” Do you mean what Toad
generates in the generated code to call the procedure? Or does the
procedure write the parameter to a database table, and you’re talking
about what winds up in that table?
Nate Schroeder
Enterprise Services - Data Management Team
Monsanto Company
800 N. Lindbergh Blvd. LC4D - Saint Louis, MO - 63167
314-694-2592
The time component is part of any value stored by Oracle in a DATE field. Just
b/c it’s not displayed, doesn’t mean it’s not there.
Are you getting a PLS- or ORA- error when you submit the program to run with the
time component?
Have you looked at your NLS settings to see how the database is rendering dates
and your Toad options to see who they are being displayed in the data grids?
What version of Toad are you using? The latest version uses your NLS_DATE
settings and turns the date from the calendar picker into a TO_DATE function
call. If you're using an older version, you can just edit the anonymous block
code.
A date field always has hours/minutes/second. There is no such thing as a short date. Time is part of a date. The data in the date field may however be truncated to a date only (no time) as part of the update process of the field.
So when executing the procedure just enter the date in the parameter and don’t enter time, leave it zero.
ETRG (Erwin The Retired Guy)
How have you entered your dates?
I had some difficulties getting exactly what I needed but I used the to_date function right in the setting of the value and now it’s showing both the date and time properly when tracing through the code.
HTH
