Query with to_date in Oracle runs differently in Windows and Ubuntu IDE

user_320253 0
Viewed 158

I have the following Oracle Query

SELECT COMPANY_NO FROM COMPANY where to_date('2020-07-20', 'yyyy-mm-dd') BETWEEN START_DATE and END_DATE;

The table was created and data was inserted using the following queries

CREATE TABLE COMPANY (COMPANY_NO varchar2(20), START_DATE varchar2(20), END_DATE varchar2(20));
INSERT INTO COMPANY (COMPANY_NO,START_DATE,END_DATE) VALUES ('Test','01.07.2020','31.07.2020');

If I execute the select query from DB Visualizer in Windows, it throws the following error - "[Code: 1843, SQL State: 22008] ORA-01843: not a valid month".

But If i execute the same query from Ubuntu IntelliJ Database IDE, it passes giving the resultset.

If the date format is not matching, then why it is passing in Ubuntu? Please suggest why this is happening and what needs to be changed.

  • Database - Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
  • Windows - Microsoft Windows 10 Enterprise
  • DB Visualizer - Free 10.0.18
  • Ubuntu - Ubuntu 16.04.6 LTS
  • IntelliJ IDEA - 2018.3.5 (Ultimate Edition)
oracle11g oracle sql
5 Answers

Your insert statement relies on implicit data type conversion based on the NLS settings of the current session (which is derived from the current operating system user)

You should never use plain strings to provide date values. Either use (unambiguous) ANSI literals (which I prefer):

INSERT INTO COMPANY (COMPANY_NO,START_DATE,END_DATE) 
VALUES ('Test', date '2020-07-01', date '2020-07-31');

or use to_date with an explicit format:

INSERT INTO COMPANY (COMPANY_NO,START_DATE,END_DATE) 
VALUES ('Test',to_date('01.07.2020', 'dd.mm.yyyy'), to_date('31.07.2020', 'dd.mm.yyyy'));

I also strongly recommend to never user to_date() with a format mask that uses names (e.g. 'dd.mon.yyyy'), rather then numbers - as those names are again subject to the current session's NLS settings.

user_3303150

I believe you need to check your date format in your tool that you use on windows.

You can check your date format with this query:

select * 
from V$NLS_PARAMETERS 
where parameter = 'NLS_DATE_FORMAT'

Here is the demo :

DEMO

In this DEMO my format is DD-MON-RR and I have tried to enter values with two different formats: Different format than the one in my parameter:

insert into test_t values('02.02.2020')

Same format:

insert into test_t values('02-JUL-2020')

Solution: try to format the date value you are inserting. You can do that in two ways:

Use the correct format or format it yourself:

insert into test_t values(to_date('02.02.2020', 'dd.mm.yyyy'))
user_65650380

First of all:

CREATE TABLE COMPANY (COMPANY_NO varchar2(20), START_DATE varchar2(20), END_DATE varchar2(20));

Why do you use VARCHAR2 datatype for date columns here? It should be

CREATE TABLE COMPANY (COMPANY_NO varchar2(20), START_DATE date, END_DATE date);

That's a bad practice to use implicit data type conversion. natural time bomb.

user_4291000

Don't use varchar2 for dates but a date/time type!

In the query you compare a date, that is produced by the to_date() to varchar2s from the table. That means, that the varchar2s need to be upcasted to dates. How the strings are interpreted to get dates from them depends on the settings what date format is used. And these can differ in different environments.

Use an appropriate data type for COMPANY.START_DATE and COMPANY.END_DATE, e.g. date. Then there's no need for the upcast and you don't compare apples and oranges.

user_96614240

If for some reason you cannot fix the data model (and I can't think why you wouldn't), your query needs to be:

select company_no from company
where  date '2020-07-20' between to_date(start_date,'DD.MM.YYYY')
                         and     to_date(end_date,'DD.MM.YYYY');

(Your original to_date('2020-07-20', 'yyyy-mm-dd') would also work fine, but date '2020-07-20' is less to type and a good habit to get into.)

user_2304710
Related