Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2011
    Posts
    9

    Unanswered: URGENT: Query records with specific date range from trnsaction Table

    Hi Frnds,

    please assist , i know this is easy but i am unable to solve it in oracle.

    i have one huge transaction table <TRANS_DTL> in this table i have one column <Trans_DT> where date is stored in format "dd-mon-yyyy hh:mi:ss"

    this table is getting filled with thousands of records every second.
    now i want to query all the data for a specific date

    please assist.

    many thanks in advance

    regards,
    Shanu

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select trunc(trans_dt), count(*) from trans_dtl group by trunc(trans_dt) order by 1;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jul 2011
    Posts
    9
    hi frnd i suppose u coudnt understand my query ....

    i need says, the all records of 26th july using column Trans_dt, where date is stored in format of dd-mn-yyyy hh:mi:ss' format.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select * from trans_dtl where trans_dt between to_date('2011-07-26','YYYY-MM-DD') AND to_date('2011-07-27','YYYY-MM-DD')
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jul 2011
    Posts
    9
    hanks my frnd,

    but i am not sure if works without leaving my records of that particular period.

    and what about the query if i store a date into a variable and , try to extract the all record of that particular date mentioned in that variable.

    please assist

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >but i am not sure if works without leaving my records of that particular period.
    I am not sure what above means

    >and what about the query if i store a date into a variable and , try to extract the all record of that particular date mentioned in that variable.
    I am not clear if you need solution for SQL or PL/SQL
    What are you contributing to this solution?
    post what you have tried & how Oracle responded
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2011
    Posts
    9
    hi dear

    >but i am not sure if works without leaving my records of that particular period.
    here my confussion is that as the table containing the data in dd-mm-yyyy hh:mi:ss' so is it sure that just by writing
    "trans_dt between to_date('2011-07-26','YYYY-MM-DD') AND to_date('2011-07-27','YYYY-MM-DD')"

    i will get all the records without ignoring any rows which would be in date range as we are not mentioning here the hours minutes and Seconds



    >and what about the query if i store a date into a variable and , try to extract the all record of that particular date mentioned in that variable.

    here i am looking for a solution in PL/SQL , as i don't know about ORACLE so looking of for your kind assistance to design a PL/SQL Block for the same problem

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    is the date stored in a string or a date variable. Makes a big difference in the query
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >i will get all the records without ignoring any rows which would be in date range as we are not mentioning here the hours minutes and Seconds

    How many rows should be returned?
    How many rows are returned?
    How will you know for sure when correct result is being returned?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jul 2011
    Posts
    9
    date is stored in to varchar

    >i will get all the records without ignoring any rows which would be in date range as we are not mentioning here the hours minutes and Seconds

    sorry for typing mistake, actually i was confirming that ">will i get all the records..." if in Tran_Dt column date is storing in DD-MON-YYYY HH:MIS' format and i am passing only dd-mon-yyy' in where clause.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then you want to run the this query

    select * from
    trans_dtl
    where trans_dt like '01-JAN-2011%';


    for example, this would return everything for january 1st of this year. and I am assuming that the date is stored in uppercase. If it is not, use upper(trans_dt) or...

    select * from
    trans_dtl
    where trans_dt like '01-jan-2011%';
    Last edited by beilstwh; 07-27-11 at 14:55.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by contact2amitesh View Post
    date is stored in to varchar
    Very bad decision.

    Dates should be stored in DATE columns!

  13. #13
    Join Date
    Jul 2011
    Posts
    9
    okay if i choose date datatype, then ??

  14. #14
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    If trans_dt is defined as a date parameter, then you could simply do the following

    Code:
    select * from
    trans_dtl
    where trans_dt >= to_date('01-jan-2011','dd-mon-yyyy')
    and trans_dt < to_date('02-jan-2011','dd-mon-yyyy');
    and if trans_dt is indexed, it will go very fast. I also agree with Shammat. Never store a date/time in anything but a date column. NEVER in a string.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  15. #15
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by beilstwh View Post
    Never store a date/time in anything but a date column. NEVER in a string.
    Just a side note: the expression to_date('01-jan-2011','dd-mon-yyyy') is subject to NLS settings (because of the month "name"). The expression might fail if the session has a non-english NLS setting as the 3-letter month name must be in the correct language.

    To make this expression more robust I strongly recommend to always use numbers, e.g. to_date('01-01-2011','dd-mm-yyyy')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •