format date with Oracle Dynamic SQL

[deadlocklegend@gmail.com]

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]

Re: format date with Oracle Dynamic SQL

deadlocklegend@ 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_colum n],'dd-Mon-yyyy HH24:MI:SS')
from your_table.

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

[deadlocklegend@gmail.com]

Re: format date with Oracle Dynamic SQL

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]

Re: format date with Oracle Dynamic SQL

deadlocklegend@ 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@gmail.com]

Re: format date with Oracle Dynamic SQL

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]

Re: format date with Oracle Dynamic SQL


<deadlocklegend @gmail.comwrote in message
news:1109731580 .120769.164910@ g14g2000cwa.goo glegroups.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]

Re: format date with Oracle Dynamic SQL


<deadlocklegend @gmail.comwrote in message
news:1109731580 .120769.164910@ g14g2000cwa.goo glegroups.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_FORMA T) 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