format date with Oracle Dynamic SQL

[deadlocklegend]

Hi all,

when i set a date field's type (12) to VARCHAR2 or STRING, I get the
date (mm/dd/yyyy) correct, but I can never get the time which is always
set to 00:00:00 even if i increase the buffer size? Is there anyway to
force pro*c to format correctly without using to_char because with
to_char i lose the ability to keep track of correct data type.

Thanks

[Frank van Bortel]

de************@gmail.com wrote:

Hi all,

when i set a date field's type (12) to VARCHAR2 or STRING, I get the
date (mm/dd/yyyy) correct, but I can never get the time which is always
set to 00:00:00 even if i increase the buffer size? Is there anyway to
force pro*c to format correctly without using to_char because with
to_char i lose the ability to keep track of correct data type.

Thanks

Dates are stored internally as numbers, and always
include a time fraction.

The display (or format) mask is what makes it visible
as a date - but you must specify it.
What you experience is the default date format, try
to select to_char([your_date_column],'dd-Mon-yyyy HH24:MI:SS')
from your_table.

All date format masks are documented; search tahiti.oracle.com
--
Regards,
Frank van Bortel

[deadlocklegend]

we are dynamically retrieving data types of a dynamic query and build
an XML resultset. to_char i believe gives us a string type when we
wanted a date type for that field. Is there a way to do it?

[Frank van Bortel]

de************@gmail.com wrote:

we are dynamically retrieving data types of a dynamic query and build
an XML resultset. to_char i believe gives us a string type when we
wanted a date type for that field. Is there a way to do it?

to_date(string, format_mask)

What other documentation shall I read you?

--
Regards,
Frank van Bortel

[deadlocklegend]

sorry about bothering you, but when i do something like

select to_date(to_char(date_column, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') from table_name

in my pro*c code, i get back

2005-02-24 00:00:00

when data is 2005-02-24 11:05:07

[Jim Kennedy]

<de************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

sorry about bothering you, but when i do something like

select to_date(to_char(date_column, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') from table_name

in my pro*c code, i get back

2005-02-24 00:00:00

when data is 2005-02-24 11:05:07
get rid of the to_date. Doesn't ProC have a native date interface?
Wouldn't it make more sence to use that?
Jim

[Mark C. Stock]

<de************@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

sorry about bothering you, but when i do something like

select to_date(to_char(date_column, 'yyyy-mm-dd hh24:mi:ss'),
'yyyy-mm-dd hh24:mi:ss') from table_name

in my pro*c code, i get back

2005-02-24 00:00:00

when data is 2005-02-24 11:05:07

the TO_DATE is using the default date format (NLS_DATE_FORMAT) to convert
back to a date column -- that is likely truncating the time element

if you want a date, don't use either to_date or to_char

if you need to convert to or from a date datatype, us to_date or to_char
(seldom are both ever used together) with the appropirate date format -- or
use ALTER SESSION to set the default date format for your session

++ mcs