Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2008
    Posts
    133
    Thanks
    14
    Thanked 0 Times in 0 Posts

    Having problems with these two basic queries

    The first one: I just need to say where is less than the current date. The following don't work:

    WHERE uio.fes_start_date < getDate() - Keep getting ''ORA-00900 - invalid identifier'
    WHERE uio.fes_start_date < now() - Keep getting 'ORA-00900 invalid identifier'


    The second query: I need to say 'where start_date is less than 31/12/10'. I have included the select statement because I am performing a to_date on it.

    SELECT To_Date (uio.fes_start_date,'DD/MM/YYY') AS START_DATE,
    WHERE uio.fes_start_date < to_date('31/12/2010','mm/dd/yyyy')

    The error message I am getting is 'ORA-01843 not a valid month'

    Thanks!

  • #2
    Regular Coder
    Join Date
    Jul 2010
    Posts
    185
    Thanks
    3
    Thanked 42 Times in 42 Posts
    There is no TO_DATE() function in MySQL. Although there is STR_TO_DATE().

    What RDBMS are you using? (Sounds like it might be Oracle).

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Since this is an Oracle error, moving from MySQL to other databases.
    For your first problem:
    I don't think Oracle has a GetDate function in it. Use sysdate instead:
    Code:
    WHERE uio.fes_start_date < sysdate
    You may need to issue a truncation on both the field and the sysdate as the sysdate for sure contains the timestamp. A simple:
    Code:
    WHERE trunc(uio.fes_start_date) < trunc(sysdate)
    Should solve that issue.

    For your second problem:
    31 is not a valid month. Check your format, the month and day blocks are wrong, flip them and I expect that should work.

    Edit:
    Beaten in. ORA error is definitely oracle, and oracle has the to_date function.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #4
    Regular Coder
    Join Date
    Aug 2008
    Posts
    133
    Thanks
    14
    Thanked 0 Times in 0 Posts
    Many Thanks all.

    Fixed and working.

    I'm used to using sql so didn't understand why it wasn't working on the oracle serve.

  • #5
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,987
    Thanks
    4
    Thanked 2,660 Times in 2,629 Posts
    Unfortunately, every DBMS has its own proprietary ways of accomplishing things. Examples of these are MySQL's LIMIT versus SQLServers TOP versus ORACLE ROWCOUNT, and Oracle's lack of PK auto_increment (unless they added in within the past few years which is possible), but instead they use a SEQUENCE which is just as dandy anyway.

    00900 is an oracle system error. Since it is, always check what it is (this is caused by incorrect SQL syntax), and start on hitting the functions for whether they are valid or not. In this case, they were not valid since the functions were wrong. These were the 'wrong' identifiers in the description. Something like a date is nice, you can just test the function in oracle:
    PHP Code:
    SELECT getDate() FROM dual
    And it should tell you that it has also failed so you know that its the getDate function causing the problem. Generally there are ways to accomplish most anything between the databases, even though some of them (a great example is Access is missing (a lot lol) a group_concat feature, and its really painful to write the vba since you cannot create aggregate functions in access vba) are very painful methods. Once you know its the getDate() function thats broken, just search google for 'Oracle equivalent to GetDate' and I'll betcha it will find tons of results.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 


  •  

    Posting Permissions

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