Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Location
    Utah
    Posts
    2

    Unanswered: writing sql for oracle and MSsql

    I am writing a java application that needs to be portable to essentially any platform/database. I am currently testing on both MS SQL Server and Oracle.

    I have encountered a discrepency between the two database. I am dynamically building a Select statement based on multiple variables, including a date field:

    Oracle: select date from table where to_char(date, 'YYYY-MM-DD') > varDate

    MS SQL: select date from table where date > varDate

    Both of these statements work fine in their respective databases. The problem is, I need ONE statement that will work for both (and potentially all) databases.

    Any suggestions?

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    From your post, I understand that varDate is not really a variable (defined as a date), but a string formatted as a date YYYY-MM-DD.

    Your two statements are not equivalent, because in the first you explicitely cast the date as a string. In the second statement, you let MSSQL do the conversion (from date to string).

    I know this is not an answer to your question, I just wanted to point that out.

  3. #3
    Join Date
    Oct 2003
    Location
    Utah
    Posts
    2
    Yes, I am converting a date to a string.

    You are also correct in noting that the examples I used are not the same. The reason I am using these two examples is because they produce results that are the same.

    I hope I did not cause anyone too much confusion on what I am looking for here: a single sql string that will work in Oracle and MS SQL Server and potentially any other database.

    I recognize that this MAY NOT be possible. If that's the case, can someone suggest a way for me to programatically differentiate between the databases in Java?

    Thanks again!

Posting Permissions

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