[thelist] SQL : A Select statement that works in SQL Server but NOT Oracle ... why ?

[rudy]

> I am trying to use the same Select statements

hi óskar

you will not always be able to use the same sql on sql/server and oracle,
especially when it comes to date fields    =o(

>  SET DATEFORMAT dmy
>  SELECT * From mytable
>     WHERE TIEINS = '1'
>       AND TIDTIM BETWEEN '01.01.2001' AND '03.01.2001'

in sql/server, this would probably work, because sql/server can recognize a
gazillion ways for you to type in a date and still recognize it (especially
if you *tell* it which format you're using)

however, i always use ISO format, without explicitly declaring DATEFORMAT
to be ymd, and it has always worked for me

   SELECT * From mySQLSERVERtable
      WHERE TIEINS = '1'
        AND TIDTIM BETWEEN '2001-01-01' AND '2001-01-03'

make sure that TIDTIM actually is declared as a datetime column, and also
that you are using the correct endpoints in your date range (remember,
sql/server datetime columns include a time, so with 00:00:00 appended to
2001-01-03 you are *not* going to get any other times on that day to
satisfy the BETWEEN condition except midnight)

now as for oracle, it doesn't have a SET DATEFORMAT command, so that will
give you trouble right there

here's what the oracle documentation says --

  "To specify a date value, you must convert a character or numeric
    value to a date value with the TO_DATE function. Oracle automatically
    converts character values that are in the default date format into date
    values when they are used in date expressions. The default date format
    is specified by the initialization parameter NLS_DATE_FORMAT and
    is a string such as 'DD-MON-YY'.

i have never tried any other format besides the default --

   SELECT * From myORACLEtable
      WHERE TIEINS = '1'
        AND TIDTIM BETWEEN TO_DATE('01 JAN 2001')
                         AND TO_DATE('03 JAN 2001')

however, i expect that the following will also work --

   SELECT * From myORACLEtable
      WHERE TIEINS = '1'
        AND TIDTIM BETWEEN TO_DATE('2001-01-01','YYYY-MM-DD')
                         AND TO_DATE('2001-01-03','YYYY-MM-DD')

again, don't forget to check your date range endpoints, as oracle stores a
time as part of the date field too


rudy.ca