Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    1

    Unanswered: Using sysdate in ODBC thru oracle

    Trying to create query to look at data from NOW minus 30 days.

    My sql looks like;

    SELECT WORKORDER.WONUM, WORKORDER.DESCRIPTION, WORKORDER.ISTASK, WORKORDER.STATUSDATE, WORKORDER.STATUS
    FROM MAX52PRD.WORKORDER WORKORDER
    WHERE (WORKORDER.ISTASK='N')
    AND (WORKORDER.STATUSDATE>={ts 'sysdate-30'})
    AND (WORKORDER.STATUS='CLOSE')

    The second to last line seems to be the problem and I can't seem to resolve it.

    I get fault message that says;

    SQL Query can't be represented graphicly.Continue anyway?

    After I Okay it I get the message;

    ORA-01841:(full) yearmust be between -4713 and +9999, and not be 0

    Thanks,
    LLozier

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The SQL language does not utilize curly braces "{}" in any context that I know about.

    Research & use the TO_DATE function.
    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
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by anacedent
    The SQL language does not utilize curly braces "{}" in any context that I know about.
    Well then you obviously don't know JDBC

    JDBC (and I believe ODBC as well) use the curly braces to define DBMS independent functions. I have no experience with ODBC but with JDBC {ts '....'} denotes a timestamp literal (to be used in a query string in Java) that will be translated to the approriate format by the driver. Thus you can eliminate DBMS specific formatting in the code.

    Now with JDBC the {ts} only takes literals, not functions so the usage inside a {ts} escape sequence is probably not supported in ODBC as well.

    But I don't see any reason why the {ts} escaping would be needed any way.

    AND (WORKORDER.STATUSDATE >= sysdate-30

    should simply work as sysdate does not need to be escaped. No need to use to_date at all.

    Thomas

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Also, unless you want to skip those records that are early in the day on the 30th day, I would write it as

    AND (WORKORDER.STATUSDATE >= trunc(sysdate-30))
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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