Howdy, Stranger!

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

Categories

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

  • infidelinfidel Member Posts: 2,900
    : 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)
  • dumitrumdumitrum Member Posts: 1
    : : 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

  • infidelinfidel Member Posts: 2,900
    : : : 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'
  • Chris BrownChris Brown USAMember Posts: 4,624 ✭✭

    ( http://forcoder.org ) free ebooks and video tutorials about \ Ruby JavaScript Java Go C++ Assembly C Perl Python R C# Scratch Visual Basic Objective-C PL/SQL MATLAB Delphi PHP Swift Visual Basic .NET Scala COBOL Lisp FoxPro Transact-SQL ABAP Lua Erlang Dart Hack Logo Awk Fortran Alice Ada Kotlin ML F# LabVIEW Bash SAS Scheme Julia VBScript Apex D Rust Crystal Clojure Prolog \ _____

Sign In or Register to comment.