Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle query using time component not returning expected results

Posted on 2011-03-02
21
Medium Priority
?
641 Views
Last Modified: 2012-05-11
I'm running this query but not getting expected results

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

The above query returns the following rows, but it's "less than" the time component:

Seqno                      Paid Dt             Load Dt                            Update Date
190000007066923      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066924      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066925      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066932      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM


Oracle-query-with-time-component.pdf
0
Comment
Question by:Alaska Cowboy
  • 8
  • 8
  • 2
  • +2
21 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35018624
and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

these don't make much sense

the first should have a to_date on it, or  better yet use a range query
also,  every time on March 2, 2011 will be truncated to be less than 3:31:42am.

trunc(noon of a day) = trunc(1am of that day) = trunc(7pm of that day) = 00:00:00 of that day


0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 200 total points
ID: 35018627
You are comparing to TRUNC(LOAD_DT).  That would make the time component midnight for comparison purposes.  If you really want less than than your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 200 total points
ID: 35018637
try this :

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and to_char(load_dt,'dd-mon-rr') = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;


0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 35018667
by a range query I mean this...

      and load_dt >= to_date( '2-mar-2011' ,'dd-mon-yyyy')
      and load_dt < to_date( '2-mar-2011' ,'dd-mon-yyyy') +1

if you only want times that occur before 3:31:42 am then don't trunc it

    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

if you have any indexes on load_dt,  you'll probably find using these 3 conditions instead of the two TRUNC calls will be faster
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018703
to_char(load_dt,'dd-mon-rr')

won't work  because the  "rr" format will produce 11,  not 2011

also,  it suffers from the same index problem that TRUNC does
you can get around that with function based indexes but range queries are more versatile
with the only cost being a few keystrokes
0
 
LVL 35

Expert Comment

by:johnsone
ID: 35018709
@ashilo

 to_char(load_dt,'dd-mon-rr') = '2-mar-2011'

????

That condition will never be true.  The TO_CHAR would output 02-mar-11, which can never match.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018736
you could change the rr to rrrr or yyyy,  but it's not an improvement over the trunc.

The data conversion should be on the constant, not the column.  That way the conversion cost is absorbed once at parse time, not once per row at runtime
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018749
sdstuber,

>>if you only want times that occur before 3:31:42 am then don't trunc it
and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018791
johnsone,

>>your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

-- but I don't want records prior to March 2
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35018862
What's the expected result for the query?
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 35018903
>>>> sdstuber,

>>>>>>if you only want times that occur before 3:31:42 am then don't trunc it
>>>>and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

>>>>but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

Check your original query posted in the question you DID put a trunc on the load_dt,

that's the problem.  because trunc(load_dt) will be 2011-03-02 00:00:00  for ALL date values of March 2.

So,   these are all the same value...
trunc(2011-03-02 00:00:00)
trunc(2011-03-02 03:31:42)
trunc(2011-03-02 15:27:14)
trunc(2011-03-02 19:00:00)
trunc(2011-03-02 23:59:59)

if you only want the load_dt values < 3:31:42,  then DON'T use trunc
if that's not what you want,  please rexplain the requirements,  or better yet

Provide sample data and expected output (preferably in text, not images, so we can import it to test with)
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018909
wdosanjos,

I have two data loads, one before 12:50am and one after 3:30am, so I'm trying to separate them.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018930
Ok, I eliminated the "trunc(load_dt) = '2-mar-2011'

I ran this

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
     and trunc(load_dt) = to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

and got 0 records returned, but you can see from my data (and the attachment on my original post) that the time component is 3:31:42

so something's not right with my time logic . . .
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 100 total points
ID: 35018975
This should give you the load before 12:50 am:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Open in new window

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018989
This also returns 0 rows

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) > to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

but the time component is clearly

This returns rows, but it sure doesn't look right:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) < to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Seq No                     Paid Dt                 Load Dt                             Update Date
140000007066963      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35018992
>>>> so something's not right with my time logic . . .

yes, exactly my point above
 DON'T USE TRUNC  - you're making ALL times equivalent by doing that.

TRUNC(sysdate)  = trunc(sysdate + 1/1000)


if my query didn't return the results you wanted,  
please explain what it did wrong or what it did not do right
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019127
oh, oops, I'm terribly sorry.

I was blind to the trunc( ) on my time component, when you all explained it I was thinking of the first trunc and didn't even see it on the time component because.

So, I'm good but thank you all for the assistance.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35019164
so the conditions I put in http:#35018667  worked then?

 great

glad I could help
please remember to close the question
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019652
increase points
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35019765
as the asker you have to increase the points if you want to.

or request attention and a Moderator will do it for you.

I can too as a Zone Advisor, but don't do so unless specifically instructed to since it's a conflict of interest as I could be rewarding myself
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 35019819
thanks for the multiple responses.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question