470,594 Members | 1,392 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

format date with Oracle Dynamic SQL

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

Jul 19 '05 #1
6 36612
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
Jul 19 '05 #2
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?

Jul 19 '05 #3
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
Jul 19 '05 #4
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

Jul 19 '05 #5

<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

Jul 19 '05 #6

<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
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by jeff | last post: by
13 posts views Thread by Roy | last post: by
3 posts views Thread by puruji | last post: by
6 posts views Thread by deadlocklegend | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.