Results 1 to 5 of 5

Thread: Dayofmonth

  1. #1
    Join Date
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34

    Unanswered: Dayofmonth

    Hi

    I'm trying to use the DAYOFMONTH function but everytime I use it I keep getting a ora-00911 error(invalid character). Any ideas as to why this is happening?

    SELECT {fn DAYOFWEEK('1997-07-16')} "DayOfWeek" FROM DUAL;

    Thanks

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by hally View Post
    Hi

    I'm trying to use the DAYOFMONTH function but everytime I use it I keep getting a ora-00911 error(invalid character). Any ideas as to why this is happening?

    SELECT {fn DAYOFWEEK('1997-07-16')} "DayOfWeek" FROM DUAL;

    Thanks
    From my perspective, you are lost, confused & totally befuddled.

    Why does post title say "dayofmonth" while the code example says "DAYOFWEEK"?

    Do you actually know what database you are (ab)using?
    Oracle? MYSQL? SQLServer?

    This forum is for ORACLE; which has neither DAYOFMONTH nor DAYOFWEEK.

    Post Operating System (OS) name & version for DB server system.
    Post results of
    SELECT * from v$version

    What problem are you really trying to solve?
    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
    Feb 2003
    Location
    Toronto, Ontario, Canada
    Posts
    34
    Sorry I wrote the wrong query down. The db i am using is Oracle 10g, on a HP unix server. The reason i'm posting this is because I read on the internet oracle had a function to return the day of date value provided but I can't seem to get it to work. I'll provide a link of the website I was reading.

    SELECT {fn DAYOFMONTH('1997-07-16')} "DayOfMonth" FROM DUAL;

    SQL Functions

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I'll provide a link of the website I was reading.
    THANKS for the URL, but please note the following
    "Oracle9i Lite SQL Reference"
    which is NOT what you are running.
    Contents
    URL above is for V10.2 Oracle & no DAYOFMONTH function exists in standard SQL for Oracle

    select to_char(to_date('07-08-09','RR-MM-DD'),'DY') DAYOFWEEK FROM DUAL

    DAYOFWEEK
    ------------
    THU

    By the way what date is '07-08-09'? Year? Month? Day?
    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
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by hally View Post
    Sorry I wrote the wrong query down. The db i am using is Oracle 10g, on a HP unix server. The reason i'm posting this is because I read on the internet oracle had a function to return the day of date value provided but I can't seem to get it to work. I'll provide a link of the website I was reading.

    SELECT {fn DAYOFMONTH('1997-07-16')} "DayOfMonth" FROM DUAL;
    You are using an JDBC (or ODBC) "escape function" syntax (See here).

    These functions denoted by {fn ...} [b]should[/i] be handled by the JDBC (or ODBC) driver, but the driver is not required to support them.
    To my knowledge the Oracle JDBC driver does not support the whole set of required JDBC escape functions (I think mostly the date literal stuff using {d ...} or {ts ..} but only very few of the other functions).

    If you are neither using JDBC (nor ODBC) that that syntax will never work as it is part of the driver, not not the backend database.

    If you are using the JDBC driver, the apparently it does not support this escape function and you need to revert to an Oracle specific function (see the link that anacedent has posted).

Posting Permissions

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