SQL & PL/SQL

I'm not sure which forum is the best for this question: I have a problem getting data from a Microsoft SQL Server database to an Oracle Database via Oracle heterogeneous services and Microsoft ODBC driver. The problem is that date formats are interpreted wrong concerning day and month, when I have tables with the old datatype datetime (instead of datetime2) on SQL-Server side but only in the where clause of an SQL query. I don’t know whether this is an Oracle problem or a Microsoft problem or whether I made a mistake in my definitions somewhere.

For a testcase I created a small table on the SQL-Server with a datetime-column and a datetime2-column:

create table mssql_test
(id       int,
 date_1   datetime,
 date_2   datetime2);

and fill both columns with the same date values:

insert into mssql_test values (1,'20260203','20260203');
insert into mssql_test values (2,'20260204','20260204');
insert into mssql_test values (3,'20260205','20260205');
insert into mssql_test values (4,'20260301','20260301');
insert into mssql_test values (5,'20260302','20260302');

On the Oracle-side I create a view which gets it’s data via database link from the SQL server:

create view oracle_test as select * from mssql_test@db_link_to_mssql;

Both SQL-Server columns are converted to Oracle DATE datatype:

desc oracle_test;

Name   Null? Typ       
------ ----- ---------- 
id          NUMBER(10) 
date_1      DATE       
date_2      DATE

and when I make a simple select both columns look identical:

select * from oracle_test
        id date_1              date_2             
---------- ------------------- -------------------
         1 03.02.2026 00:00:00 03.02.2026 00:00:00
         2 04.02.2026 00:00:00 04.02.2026 00:00:00
         3 05.02.2026 00:00:00 05.02.2026 00:00:00
         4 01.03.2026 00:00:00 01.03.2026 00:00:00
         5 02.03.2026 00:00:00 02.03.2026 00:00:00

But when I limit the result set with a “where-clause” on the date columns the two columns behave differently:

select * 
from oracle_test 
where "date_2" between to_date('03.02.2026','dd.mm.yyyy') 
                   and to_date('05.02.2026','dd.mm.yyyy');

        id date_1              date_2             
---------- ------------------- -------------------
         1 03.02.2026 00:00:00 03.02.2026 00:00:00
         2 04.02.2026 00:00:00 04.02.2026 00:00:00
         3 05.02.2026 00:00:00 05.02.2026 00:00:00

select * 
from oracle_test 
where "date_1" between to_date('03.02.2026','dd.mm.yyyy') 
                   and to_date('05.02.2026','dd.mm.yyyy');

        id date_1              date_2             
---------- ------------------- -------------------
         5 02.03.2026 00:00:00 02.03.2026 00:00:00

So with the old datetime column on MSSQL side the day and month are interpreted in reverse order and the format string is ignored.

The versions are:

• Microsoft SQL Server 2022 Standard Edition (64-bit) CU22 16.0.4225.2
• Oracle Database 19c SE2 Version 19.29
• Microsoft ODBC Driver for SQL Server, Version 17.05.0002