Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Angry Unanswered: Experts Need help on dates..YrtoDate, Last12Mths

    Hello experts I was wondering is someone could help me out ASAP.

    Need help figuring out YeartoDate and Last12Months syntax.

    YearToDate = I have come up with the following..not sure if it's right.

    select * from table where convert_to_epoch(arrival_time) between TRUNC(SYSDATE,'YEAR') and sysdate;

    ALSO

    Last12Months

    select * from table where convert_to_epoch(arrival_time) between last_day(add_months(sysdate, -12)) + 1 and sysdate;

    Now if this is correct why do I get the error Date Format Picture Ends before converting entire Input String. My epoch function converts field to format 30-OCT-2000 10:05:47


    Lastly, I am trying to find data for Current week(which is technically the prior week from current starting on Sat - Sun. Now my problem is the data within the date range specified is not being returned correctly. This is the code.

    SELECT case_id_, to_date_from_epoch(arrival_time) as arrival_time FROM hpd_helpdesk
    where to_date_from_epoch(arrival_time) >= to_char(sysdate-to_number(to_char(sysdate,'D'))-7+1,'DD-MON-YYYY hh:mi:ss')
    AND to_date_from_epoch(arrival_time) <= to_char(sysdate-to_number(to_char(sysdate,'D')),'DD-MON-YYYY hh:mi:ss');

    Why do I get data for 09-NOV-2000 07:47:03 coming back when it specifically says to return data for April 9 - 15, 2006? Thanks again for your help. Thank you for all your help.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends upon what is, is.
    >30-OCT-2000 10:05:47
    I suspect that part of the problem is that the line immediately above is a STRING
    You may want to learn & appreciate the difference between a string (a.k.a. VARCHAR2) and a DATE datatype.
    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
    Apr 2006
    Posts
    140

    Angry The epoch conversion is a string

    Hello Anacedent.

    The 30-OCT-2000 10:05:47 is a string. Sorry still trying to learn.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    what datatype is "arrival_time"?
    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
    Apr 2006
    Posts
    140

    Angry Arrival_time is a number

    Arrival_time is a number. Thanks again.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    TO_DATE(convert_to_epoch(arrival_time),'DD-MON-YYYY HH24:MIS')
    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
    Apr 2006
    Posts
    140

    Well tried it and it still produces an error

    Hello Anacedent,

    I tired what you suggested and still received the error. Here is my code
    SELECT case_id_ FROM hpd_helpdesk
    where TO_DATE(to_date_from_epoch(arrival_time),'DD-MON-YYYY HH24:MIS') between to_char(sysdate-to_number(to_char(sysdate,'D'))-7+1,'DD-MON-YYYY hh:mi:ss')
    and to_char(sysdate-to_number(to_char(sysdate,'D')),'DD-MON-YYYY hh:mi:ss');

    Any other suggestions?
    I am getting error ORA-08130:date format picture ends before converting entire input string

    Thanks again

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you really need to take the time to RTFM
    http://download-west.oracle.com/docs...3.htm#i1003589
    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.

  9. #9
    Join Date
    Apr 2006
    Posts
    140

    Definitely can appreciate what your saying

    Thanks again antecedant,

    I definitely can appreciate what your saying and have been looking at the diff from To_char, to_date, varchar 2 etc. My problem is that i might not be understanding when I'm thinking I understand if that makes any sense.
    I have broken up both values using to_char together and to_date so that everything coincides and still no luck. I tried to dissect the code, first the function to see what get's returned using both to_chr and to_date and then breaking up my date range and just when I think I am doing it right it's still wrong. Anyways thanks for your help.

  10. #10
    Join Date
    Apr 2006
    Posts
    140

    Angry Ok one last question just again because I think I understand

    O.K antacedent,

    Once again I have been reading and finally got my code to work when the 2 conditions are equal.

    SELECT to_char(CONVERT_UTC_DATE(arrival_time,'MDT'), 'DD-MON-YY') from hpd_helpdesk
    where (to_char(CONVERT_UTC_DATE(arrival_time,'MDT'), 'DD-MON-YY') = to_char(sysdate-to_number(to_char(sysdate,'D'))-31+1,'DD-MON-YY'));

    data for 23-Mar-06 get's returned successfully. Now If I put a Greater then Sign inbetween the conditions > The data returned is wrong. I just wanted to double check if something is up with my oracle if I still need to read

  11. #11
    Join Date
    Apr 2006
    Posts
    140

    Smile ahaha I figured it out

    Well,

    I fugured it out Antacedant and still trying to grasp my head as to tpyes that are being returned. It's not my oracle but just my understanding. I changed all to_char to to_dates and it worked. Sorry for the last post.

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    when TO_CHAR is used you are comparing strings
    when comparing strings JAN > FEB
    when comparing dates as string you need to use YYYY-MM-DD format to obtain the necessary collating sequence.
    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.

  13. #13
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    When you look at a string like '09-NOV-2000 07:47:03', you might immediately recognise it as a date and therefore be able to tell that it is earlier than '01-DEC-2000 19:47:03', but to a computer it is a string like any other and it will sort it in phonebook order, unless you specifically tell it otherwise.

    Fortunately Oracle has a DATE datatype, and DATE columns can be sorted and compared in date order as you would expect.

    Now if ARRIVAL_TIME is stored (unhelpfully) as a NUMBER, and you have a function CONVERT_TO_EPOCH that converts a number to (unhelpfully) a string, you are going to have to wrap the whole CONVERT_TO_EPOCH(arrival_time) expression in a TO_DATE function.

    Be careful not to get carried away and apply TO_DATE to things that are already dates though. It may appear to have worked but it can have unintended side effects.

  14. #14
    Join Date
    Apr 2006
    Posts
    140

    Talking Perfect

    This makes total sense. Thank Antacedant. Thank you to William also for that great clarification. Really helps me when trying to learn Oracle. Thanks agin to all.

Posting Permissions

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