[NTLUG:Discuss] OT Oracle date question on UNIX with OCI

[Eric Schnoebelen]

Fred James writes:
- Eric Schnoebelen wrote:
- >Fred James writes:
- >- select date ..... and then pass it to an external procedure (OCI) ... 
- >- but in the end I need to print the date and time as "mm/dd/yyyy 
- >- HH:MM:SS" ... right now all I can get is "DD-MON-YY"
- >
- >are you returning a date type from your external proceedure?
- >
- >If so, use the SQL function to_char() to format it in the
- >fashion you wish (assuming this is in PL/SQL or SQL.)
- >
- >The interesting arguments to to_char are:
- >
- >	to_char(<variable>, <format string>)
- >
- >eg:
- >	to_char(<variable>, 'MM/DD/YYYY HH12:MI:SS');
- 
- In this case a PL/SQL procedure calls the external procedure 
- (package/package body) with a number of parameters/arguments, one of 
- which is a date.  At the moment the external procedure is receiving it 
- as a VARCHAR2 (STRING).  I have tried defining it as DATE (DATE), but 
- that returns compilation errors.

Are you trying to print it from of your external procedure, or
are you passing the results back to PL/SQL, for interpretation
as a DATE type?

If  you're passing and returning a date parameter as a string (which
I think you need to do), you will probably need to use 'to_date()'
or 'to_datetime()' to turn it back into a date(time), which you
can then manipulate using to_char().

(hmm, if I remember correctly, PL/SQL will cast a string of the
format 'DD-MON-YY' into a date(time) automatically.)

--
Eric Schnoebelen		eric at cirr.com		http://www.cirr.com
    Glossary:  compu-dinosaur: N, A computer guru stuck in time. (is
    happy with a TeleVideo terminal and a modem).  Most probably the
	      guy you learned everything you know from.