OraFAQ Forum: SQL & PL/SQL » Date type strange behavior

Home » SQL & PL/SQL » SQL & PL/SQL » Date type strange behavior

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

Date type strange behavior [message #24023]

Tue, 21 January 2003 10:26

Igor Starodubtsev
Messages: 3
Registered: January 2003

Junior Member

Setup:
SQL> describe is_date_issue;
Name Null? Type
------------- -------- ----
MONTH_DT NOT NULL DATE
CYCLE_ID NOT NULL NUMBER

This is a big partitioned table with all correct data
except just one cycle_id, say cycle_id = 13.

Any query with to_date conversion like:

SQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and month_dt =
4 to_date('01-JAN-2003','DD-MON-YYYY');

will return

COUNT(*)
--------
0

Only something like the following

SQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and trunc(month_dt) =
4 to_date('01-JAN-2003','DD-MON-YYYY');

will return data:

COUNT(*)
--------
123

The conversion of month_dt to char will also work, but it's not the point how to make it work.

The question is why Oracle doesn't recognize month_dt the regular way, as for any other cycle_id?

Report message to a moderator

Re: Date type strange behavior [message #24025 is a reply to message #24023]

Tue, 21 January 2003 10:49

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

Because month_dt must have a time component to it. When you're saying

SQL> select count(*) from is_date_issue
2 where cycle_id = 13
3 and month_dt = 
4 to_date('01-JAN-2003','DD-MON-YYYY');

you are looking for all rows whose month_dt is equal to midnight on the morning of January 1st, 2003. If all month_dts have a time component to them, even if their date components are all 01-JAN-2003, then the condition of equality will not be met and you'll get no rows returned.

What TRUNC is doing for you is zeroing any DATE's time component back to midnight, hence you're getting your matches with TRUNC.

Try running this query; it should list the hours of the day (members of the set 00, 01, 02, .., 22, 23) and the quanities of is_date_issue rows whose time components fall within those hours of the day.

SELECT RPAD(TO_CHAR(idi.month_dt,'HH24'),4) hour
,      COUNT(TO_CHAR(idi.month_dt,'HH24'))  qty
FROM   is_date_issue   idi
WHERE  idi.month_dt IS NOT NULL
AND    idi.cycle_id = 13
GROUP BY RPAD(TO_CHAR(idi.month_dt,'HH24'),4)

If they're all midnight, you'll get one row for the hour "00"; if not, the rows will be otherwise dispersed throughout the day.

Hope this helps,

A

Report message to a moderator

Re: Date type strange behavior [message #24030 is a reply to message #24023]

Tue, 21 January 2003 11:36

Igor Starodubtsev
Messages: 3
Registered: January 2003

Junior Member

All rows for that cycle are set to
'01-JAN-2003 00:00:00' as for any other cycle.
Therefore, trunc() shouldn't make any difference.
It appeares could be some other garbage in that field
and as a precausion it's better to truncate it anyway.
Thank you for your help.

Report message to a moderator

Re: Date type strange behavior [message #24032 is a reply to message #24023]

Tue, 21 January 2003 11:57

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

_quote:
----------------------------------------------------------------------

All rows for that cycle are set to 
'01-JAN-2003 00:00:00' as for any other cycle.
Therefore, trunc() shouldn't make any difference.
It appeares could be some other garbage in that field
and as a precausion it's better to truncate it anyway.
Thank you for your help.

----------------------------------------------------------------------

That doesn't make any sense.

First of all, what do you get when you do

SELECT COUNT(*)
FROM   is_date_issue   idi
WHERE  idi.cycle_id = 13
AND    idi.month_dt BETWEEN TO_DATE('20030101'
                            ,       'YYYYMMDD')
                        AND TO_DATE('20030101235959'
                            ,       'YYYYMMDDHH24MISS')

? And secondly, what do you get when you do

                            
SELECT COUNT(*)
FROM   is_date_issue   idi
WHERE  idi.cycle_id = 13
AND    idi.month_dt BETWEEN TO_DATE('20030101000001'
                            ,       'YYYYMMDDHH24MISS')
                        AND TO_DATE('20030101235959'
                            ,       'YYYYMMDDHH24MISS')

?

Art

Report message to a moderator

Previous Topic:	Date Arithmetic
Next Topic:	Help with dbms_sql.define_column in a loop

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 19:17:29 CDT 2024