Page 1 of 3 123 LastLast
Results 1 to 15 of 32

Thread: datetime

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: datetime

    Hi guys

    Can I and if so how can I specifiy whole months to be selected in the query?

    Like

    select * from table.schema
    where datetime = (one whole month)
    or
    where datetime between 00:00:00 01 JAN 09 and 00:00:00 31 JAN 09.

    or datetime > trunc(sysdate)-30.

    The problem is where every month has 30 days, not every month has 31 days not to mention the one month with 28 days.

    Kindly advise please.............

    Regards
    Shajju

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Every month begins on the first of the month
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You might want to look into the EXTRACT function.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    There's a LAST_DAY( ) function in 10g, but, you'll still need to concatenate 23:59:59 onto the that date to get the true end-of-the-day.

    --=cf

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Your initial question is not clear if you want "one month from today" or "one specific month".

    If you just want one specific month, you can use
    Code:
    WHERE extract(month from your_date_column) = 1
    to select January.


    If you want "one month from today", you can use
    Code:
    WHERE your_date_column <= sysdate + interval '1' month
    Here you'll need to take the time part into account as well, but unless you are more specific about your problem it's hard to tell.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by chuck_forbes
    There's a LAST_DAY( ) function in 10g
    Actually, LAST_DAY existed in 8.1.7 (at least).

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks for all the replies guys.

    It's like on the 1st of every month, I want the query to return data for the previous month.

    e.g.,

    Where datetime = 'The previous whole month'

    Regards

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    One option might be
    Code:
    SQL> select trunc(add_months(sysdate, -1), 'mm') first_of_previous_month,
      2         trunc(last_day(add_months(sysdate, -1))) last_of_previous_month
      3  from dual;
    
    FIRST_OF_P LAST_OF_PR
    ---------- ----------
    01.09.2009 30.09.2009
    Another one, probably worse, would consider
    Code:
    where to_char(your_date_column, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')

  9. #9
    Join Date
    Aug 2008
    Posts
    464
    When I used

    Code:
    WHERE your_date_column <= sysdate + interval '1' month
    I got data for the last 3 months.

  10. #10
    Join Date
    Aug 2008
    Posts
    464
    Code:
    SQL> select trunc(add_months(sysdate, -1), 'mm') first_of_previous_month,
      2         trunc(last_day(add_months(sysdate, -1))) last_of_previous_month
      3  from dual;
    works but I don't want to use 'Sysdate'...I just want to end the period with 23:59:59 (last day of the month).

  11. #11
    Join Date
    Aug 2008
    Posts
    464
    Code:
    where to_char(your_date_column, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy')
    Looks like using the above, no matter on which day of the month I run the query, the result will always return the previous whole month. correct?

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Correct, because your request (as stated in a message #7) says "Where datetime = 'The previous whole month'".

    Any month (this, previous, next, last year's, ...) is detected by SYSDATE. So why do you not want to use this function? In my opinion, relying on its value is a better idea than entering (or hard-coding) these values.

  13. #13
    Join Date
    Aug 2008
    Posts
    464
    Absolutely.

    where to_char(your_date_column, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') just fine for me.

    Thanks a lot.

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by shajju
    Absolutely.

    where to_char(your_date_column, 'mm.yyyy') = to_char(add_months(sysdate, -1), 'mm.yyyy') just fine for me.

    Thanks a lot.
    While it gives the desired results, it forces a Full Table Scan every 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.

  15. #15
    Join Date
    Dec 2003
    Posts
    1,074
    I guess I meant that I knew that LAST_DAY() was in 10g, as that's the version I use (& started with).

    This should allow you to use indexes on your date column, if there is one there now (or one is added in the future):

    Code:
    select * 
    from schema.table 
    where date_column between 
           TO_DATE(TO_CHAR(ADD_MONTHS(sysdate, -1),'MM')||'/1/'||TO_CHAR(ADD_MONTHS(sysdate, -1),'YYYY'),'MM/DD/YYYY') and
           TO_DATE(TO_CHAR(LAST_DAY(ADD_MONTHS(sysdate, -1)),'MM/DD/YYYY')||'23:59:59','MM/DD/YYYY HH24:MI:SS');
    No matter what day you run it on, it'll pick up the first & last day of the month immediately prior.

    You can even shorten the first expression to

    Code:
    TO_DATE(TO_CHAR(ADD_MONTHS(sysdate, -1),'MM/YYYY'),'MM/YYYY')
    Since without mentioning the DAY, when the TO_DATE() function is applied it assumes the first day, but then you're leaving your code to be controlled by the default behavior in Oracle, which *could* change.

Posting Permissions

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