Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2012
    Posts
    16

    Unanswered: Order by Month/Yr order ... Dec/11 Jan/12 Feb/12 ect.

    The query I have is:


    select to_CHAR(date_time,'Mon-YYYY') AS DATES,
    count (distinct extract (day from date_time)) as NumberofDays
    FROM Table_Names
    WHERE (date_time >=
    '1-Dec-2011'--TO_DATE (:dtstart, 'mm/dd/yyyy hh24:mi:ss')
    )
    AND (date_time <= '31-Mar-2012'--TO_DATE (:dtend, 'mm/dd/yyyy hh24:mi:ss')
    Group by to_CHAR(al.attempt_date_time,'Mon-YYYY')
    ORDER BY (to_CHAR(al.attempt_date_time,'Mon-YYYY'))

    ^^ gives me

    Dec-2011 29
    Feb-2012 28
    Jan-2012 29
    Mar-2012 26

    I want it to be:
    Dec-2011 29
    Jan-2012 29
    Feb-2012 28
    Mar-2012 26

    I added To_Date
    Group by to_CHAR(al.attempt_date_time,'Mon-YYYY')
    ORDER BY To_Date(to_CHAR(al.attempt_date_time,'Mon-YYYY'))


    It say processing ....... execution time .... then pops up

    A non-numeric character was found wehre a numeric was expected
    Last edited by SQLNoob8; 06-14-12 at 14:58.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >ORDER BY To_Date(to_CHAR(al.attempt_date_time,'Mon-YYYY'))

    why only single mask when two functions are used
    what results when you do as below?

    ORDER BY AL.ATTEMPT_DATE_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.

  3. #3
    Join Date
    Jun 2012
    Posts
    16
    "not a Group by expression"

    I just want to put the output in Mon/Yr order

  4. #4
    Join Date
    Jun 2012
    Posts
    16
    If I change the top and

    bottom to this:

    Group by to_char(al.attempt_date_time,'Mon'), extract (year from al.attempt_date_time)
    ORDER BY extract (year from al.attempt_date_time), to_date(to_char(al.attempt_date_time,'Mon'),'MM')


    It would work, but I would have 2 columns one for Months and one for Years

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it works better/easier if output can be displayed as below

    YYYY-MM

    then sort/collating sequence maintains desired row order
    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.

  6. #6
    Join Date
    Jun 2012
    Posts
    16
    ^^ THANKS!

    Changed to this and it works:

    Group by to_char(al.attempt_date_time,'YYYY-Mon')
    ORDER BY to_date(to_char(al.attempt_date_time,'YYYY-Mon'),'YYYY-Mon')

Posting Permissions

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