OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » Date problem

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date problem [message #89664]

Thu, 17 June 2004 05:01

kim
Messages: 116
Registered: December 2001

Senior Member

Hi,

Got a report where two parameters should be used as from and to date. The input format is dd-mon-yy, 17-JUN-04.

I'm trying to convert data to 17-06-2004 by using to_date('17-JUN-04', 'dd-mm-rrrr'). But when running the report it's still 17-JUN-04.

But when doing 'select to_date('17-JUN-04', 'dd-mm-rrrr') from dual' using toad I'll get 17-06-2004.

How come ?

Regards

Kim

Report message to a moderator

Re: Date problem [message #89665 is a reply to message #89664]

Thu, 17 June 2004 06:49

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

You say you're "trying to convert data to 17-06-2004"....why?

I'm assuming that in your database's table/view, the date field is stored as a DATE datatype.

When you accept user parameters for this report, you're accepting them in VARCHAR2 format. You say the input format is 17-JUN-04 (or, DD-MON-RR¹).

It would seem to me like your SELECT statement should be

SELECT t.col1
,      t.col2
,      ....
FROM   t
WHERE  t.date_field BETWEEN TO_DATE(p_from_date_parameter,'DD-MON-RR')
                        AND TO_DATE(p_to_date_parameter,'DD-MON-RR')
/

...or, if the DATEs in your tables also include a time component,

SELECT t.col1
,      t.col2
,      ....
FROM   t
WHERE  t.date_field BETWEEN TO_DATE(p_from_date_parameter,'DD-MON-RR')
                        AND TO_DATE(p_to_date_parameter || '235959','DD-MON-RRHH24MISS')
/

Am I missing something?

A.

----------------------------------------------------------------------
¹Shouldn't you be using four-digit years?

Report message to a moderator

Previous Topic:	Number of characters in field
Next Topic:	Parameterized report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 06:38:23 CDT 2024