Independent SQL Syntax

Hi,

I am in the midst of making a application that is to be database-independent. I am facing a problem on the date comparison. Here are the following statements that i have tried to query the database...

SELECT * FROM tbl_order WHERE dateOrdered = #27/11/2002#;
SELECT * FROM tbl_order WHERE dateOrdered = '27/11/2002';
SELECT * FROM tbl_order WHERE dateOrdered = {d '27/11/2002'};

but none of them work for all the databases. I am testing these SQL statements on MS SQL Server, DB2, and Oracle v8. Please advise me on the syntax of the Date Comparison asap. Thanks.

Regards,
nakita_andrea

Comments

  • : SELECT * FROM tbl_order WHERE dateOrdered = #27/11/2002#;
    : SELECT * FROM tbl_order WHERE dateOrdered = '27/11/2002';
    : SELECT * FROM tbl_order WHERE dateOrdered = {d '27/11/2002'};
    :
    : but none of them work for all the databases. I am testing these SQL statements on MS SQL Server, DB2, and Oracle v8. Please advise me on the syntax of the Date Comparison asap. Thanks.

    I'm surprised the second one doesn't work. Perhaps you need to use American date format? (MM/DD/YYYY rather than DD/MM/YYYY)
  • : : SELECT * FROM tbl_order WHERE dateOrdered = #27/11/2002#;
    : : SELECT * FROM tbl_order WHERE dateOrdered = '27/11/2002';
    : : SELECT * FROM tbl_order WHERE dateOrdered = {d '27/11/2002'};
    : :
    : : but none of them work for all the databases. I am testing these SQL statements on MS SQL Server, DB2, and Oracle v8. Please advise me on the syntax of the Date Comparison asap. Thanks.
    :
    : I'm surprised the second one doesn't work. Perhaps you need to use American date format? (MM/DD/YYYY rather than DD/MM/YYYY)
    :
    I don't think that on Oracle will work because Oracle stores dates in a binary format (8 Bytes i think) and you have to use the TO_DATE(...)/TO_CHAR(..) functions to convert the date .See the Oracle documentation for details

  • : : : SELECT * FROM tbl_order WHERE dateOrdered = #27/11/2002#;
    : : : SELECT * FROM tbl_order WHERE dateOrdered = '27/11/2002';
    : : : SELECT * FROM tbl_order WHERE dateOrdered = {d '27/11/2002'};
    : : :
    : : : but none of them work for all the databases. I am testing these SQL statements on MS SQL Server, DB2, and Oracle v8. Please advise me on the syntax of the Date Comparison asap. Thanks.
    : :
    : : I'm surprised the second one doesn't work. Perhaps you need to use American date format? (MM/DD/YYYY rather than DD/MM/YYYY)
    : :
    : I don't think that on Oracle will work because Oracle stores dates in a binary format (8 Bytes i think) and you have to use the TO_DATE(...)/TO_CHAR(..) functions to convert the date .See the Oracle documentation for details

    Actually I think Oracle stores dates in a string format. If you're using an Oracle database, queries should format dates like: '01-Nov-2002'
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories