Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    26

    Unanswered: Date functions in Oracle

    Hi All:
    Please note that I am having a sql query for MySQL where I am using mysql specific date function.The query is like this:
    "SELECT month(prDate) month , year(prDate) year from ........"

    but in case of oracle there are no such functions available.

    is it possible for some one to suggest some equivalent oracle query for this.

    Thanks in advance:

    -Ravi

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Date functions in Oracle

    Not sure what the month() function returns, but you can convert an Oracle date to a character using the TO_CHAR funciton

    if Date_Field is Oracle Date Datatype and is set to 1/1/2003

    To_Char(Date_Field,'MM') = 01
    To_Char(Date_Field,'MON') = JAN
    To_Char(Date_Field,'YY') = 03
    To_Char(Date_Field,'YYYY') = 2003

  3. #3
    Join Date
    Sep 2003
    Posts
    26

    Re: Date functions in Oracle

    Originally posted by carloa
    Not sure what the month() function returns, but you can convert an Oracle date to a character using the TO_CHAR funciton

    if Date_Field is Oracle Date Datatype and is set to 1/1/2003

    To_Char(Date_Field,'MM') = 01
    To_Char(Date_Field,'MON') = JAN
    To_Char(Date_Field,'YY') = 03
    To_Char(Date_Field,'YYYY') = 2003
    Hi Thanks for your reply but the problem what I am facing is:
    In case of mysql the sql query which I am using is like this :

    "SELECT month(xDate) month,year(xDate) year ,count(*) FROM xTable WHERE x = ? GROUP BY month(xDate) ,year(XDate) ORDER BY year(xDate) DESC ,month (xDate) DESC"


    Now in case of Oracle if I am replacing the date functions with To_Char the query comes out like :

    SELECT TO_CHAR(xDate,'MM') month ,TO_CHAR(xDate,'YYYY') ,count(*) FROM xTable where x = ? GROUP BY TO_CHAR(xDATE,'MM') ,To_CHAR(xDate,'YYYY') ORDER BY TO_CHAR(xDate,'YYYY') ,TO_CHAR(xDate,'MM');

    This is resulting in not a GROUP BY expression exception,
    can you suggest some thing for this ?

  4. #4
    Join Date
    Oct 2003
    Location
    Colorado
    Posts
    4
    The only problem I see with your query is the WHERE clause.

    WHERE X = ? isn't standard SQL.

    Here's an example of your query without the WHERE clause (and a different table name) that does not give any error:

    SELECT TO_CHAR(xDate,'MM') month ,TO_CHAR(xDate,'YYYY') ,count(*)
    FROM test1
    GROUP BY TO_CHAR(xDATE,'MM') ,To_CHAR(xDate,'YYYY')
    ORDER BY TO_CHAR(xDate,'YYYY') ,TO_CHAR(xDate,'MM')
    /

  5. #5
    Join Date
    Sep 2003
    Posts
    26
    Originally posted by gcd
    The only problem I see with your query is the WHERE clause.

    WHERE X = ? isn't standard SQL.

    Here's an example of your query without the WHERE clause (and a different table name) that does not give any error:

    SELECT TO_CHAR(xDate,'MM') month ,TO_CHAR(xDate,'YYYY') ,count(*)
    FROM test1
    GROUP BY TO_CHAR(xDATE,'MM') ,To_CHAR(xDate,'YYYY')
    ORDER BY TO_CHAR(xDate,'YYYY') ,TO_CHAR(xDate,'MM')
    /
    Thanks
    you are right the problem was in where clause only.
    Thanks for the reply.

    -Ravi

Posting Permissions

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