#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    5
    Rep Power
    0

    Angry Oracle 9i query help!!


    need help with a query. say my table which is called shipment is as follows:

    ship_id, inv_id, date_recieved

    my problem is that how do i retrieve all values where shipment was recieived during October 2003 using ADD_MONTHS fuction?? please help!!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Apr 2002
    Posts
    693
    Rep Power
    26
    I don't think add_months is the correct function here, I think you want to use trunc

    ie.


    PHP Code:
    select from shipment
    where trunc
    (date_received,'month') = to_date('10-2003','mm-yyyy'
    Now I didn't check if i have the exact correct parameters, but this should give you the idea.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    hedge's solution works perfectly. BTW, the query will also work when replacing 'month' with 'mon' or 'MM'.

    Cheers,
    Dan
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    12
    Another way to achieve it is by using the relatively new extract function. The query is a bit more explicit than the previous solution:

    select * from shipment
    where extract(month from date_received) = 10
    and extract(year from date_received) = 2003

    Cheers,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo