Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Question Unanswered: DATETIME compare

    Hi,

    I am getting "quoted string not properly terminated" error while executing the following query.

    DELETE FROM il50.CDF_FILEDESCRIPTION F WHERE EXISTS (SELECT * FROM il50.CDF_HEADER H WHERE F.BATCHID = H.BATCHID AND H.FINISHTIME <= '2001-01-01 00:00:00')

    Can't I compare the date column as shown above?

    Thanks
    Sateesh.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    <= TO_DATE('2001-01-01 00:00:00','YYYY-MM-DD HH:MIS') ...

    Oracle format is nomally dd-mon-yy .... for date columns

    HTH
    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Gregg, I am getting the same error. Is my comparision correct?

    Sateesh.

    Originally posted by gbrabham
    <= TO_DATE('2001-01-01 00:00:00','YYYY-MM-DD HH:MIS') ...

    Oracle format is nomally dd-mon-yy .... for date columns

    HTH
    Gregg

  4. #4
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: DATETIME compare

    Use to_date function here '2001-01-01 00:00:00'

    so:

    SQL> SELECT
    2 to_date('2001-01-01 00:00:00','YYYY-MM-DD HH24:MIS')
    3 from dual;

    TO_DATE(
    --------
    01/01/01

    SQL>
    Joel Pérez

  5. #5
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: DATETIME compare

    Joel Pérez

  6. #6
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re: DATETIME compare

    SQL> SELECT
    2 to_date('2001-01-01 00:00:00','YYYY-MM-DD HH24:MIS')
    3 from dual;

    TO_DATE(
    --------
    01/01/01

    SQL>
    SQL> create table my_date ( c1 date);

    Table created.

    SQL> select * from my_date
    2 where c1 <= to_date('2001-01-01 00:00:00','YYYY-MM-DD HH24:MIS');

    no rows selected

    SQL>
    Joel Pérez

  7. #7
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    "quoted string not properly terminated"

    Are you sure you are using single quotes(') not double quotes (")
    and you don't have an extra quote somewhere ???

    Gregg

  8. #8
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    Thanks all, it worked fine.

    Sateesh.

    Originally posted by gbrabham
    "quoted string not properly terminated"

    Are you sure you are using single quotes(') not double quotes (")
    and you don't have an extra quote somewhere ???

    Gregg

  9. #9
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re:

    Reply how do you fix it. Please
    Joel Pérez

  10. #10
    Join Date
    Jan 2004
    Location
    Venezuela
    Posts
    151

    Re:

    Reply how do you fixed it. Please
    Joel Pérez

  11. #11
    Join Date
    Nov 2003
    Location
    India
    Posts
    28

    Re:

    DELETE FROM il50.CDF_FILEDESCRIPTION F WHERE EXISTS
    (SELECT * FROM il50.CDF_HEADER H WHERE F.BATCHID = H.BATCHID AND H.STARTTIME <= TO_DATE('2001-01-01 01:00:00','YYYY-MM-DD HH:MIS'))

    I modified the query as above and it worked.

    Thanks
    Sateesh.

    Originally posted by joelperez
    Reply how do you fix it. Please

Posting Permissions

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