CODEWITHSUNDEEP
sqloracle-database
SqueakyDeath
SqueakyDeath

Reputation: 41

error when I filter select statement by date

I received a script from a work colleague to run when checking batches however the script works on his laptop but as soon as I run it on my device I get and error. I don't know if it could be permissions since that's the only difference between our setups.

the script is to select certain columns convert date format with to_char for specific batch name and then filter for specific date time. here is the sample code:

select batch_name, NO_OF_ITEMS, NUMBER_OF_ERRORS, to_char(RUN_START_DATE,'DD-MON-YYYY HH24:MI') EXECUTION_START, to_char(RUN_END_DATE,'DD-MON-YYYY HH24:MI') EXECUTION_END,
to_char(START_DATE,'DD-MON-YYYY HH24:MI') START_DATE, to_char(END_DATE,'DD-MON-YYYY HH24:MI') END_DATE, NOTES from bat_log
where BATCH_NAME in (
'SJ63R_03'
) 
and RUN_START_DATE between '04-Aug-2020 07:00' AND '05-Aug-2020 07:00'
order by run_start_date desc;

error I get:

ORA-01830: date format picture ends before converting entire input string
01830. 00000 -  "date format picture ends before converting entire input string"
*Cause:    
*Action:

when I remove the time 07:00 from RUN_START_DATE between '04-Aug-2020 07:00' AND '05-Aug-2020 07:00' the script runs perfectly but obviously doesn't filter for the time frame.

any suggestions would be appreciated.

Upvotes: 0

Views: 134

Answers (2)

Roberto Hernandez
Roberto Hernandez

Reputation: 8518

I tried to replicate a bit your scenario

SQL> create table t ( c1 date , c2 date ) ;

Table created.

SQL> insert into t values ( to_date('01/08/2020 11:00:00','DD/MM/YYYY HH24:MI:SS') , to_date('01/08/2020 17:00:00','DD/MM/YYYY HH24:MI:SS') ) ;

1 row created.

SQL> insert into t values ( to_date('01/09/2020 11:00:00','DD/MM/YYYY HH24:MI:SS') , to_date('01/09/2020 17:00:00','DD/MM/YYYY HH24:MI:SS') ) ;

1 row created.

SQL> select * from t ;

C1        C2
--------- ---------
01-AUG-20 01-AUG-20
01-SEP-20 01-SEP-20

SQL> select c1 , c2 from t where to_char(c1,'DD-MON-YYYY HH24:MI') between '02-AUG-2020 08:00' and '02-SEP-2020 08:00' ;

no rows selected

SQL> select c1 , c2 , to_char(c1,'DD-MON-YYYY HH24:MI') as result from t ;

C1        C2        RESULT
--------- --------- --------------------------
01-AUG-20 01-AUG-20 01-AUG-2020 11:00
01-SEP-20 01-SEP-20 01-SEP-2020 11:00

SQL> select c1 , c2 from t where to_char(c1,'DD-MON-YYYY HH24:MI') between to_date('02-AUG-2020 08:00','DD-MON-YYYY HH24:MI')
  2  and to_date('02-SEP-2020 08:00','DD-MON-YYYY HH24:MI') ;
select c1 , c2 from t where to_char(c1,'DD-MON-YYYY HH24:MI') between to_date('02-AUG-2020 08:00','DD-MON-YYYY HH24:MI')
                            *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> alter session set nls_date_format='DD-MON-YYYY HH24:MI' ;

Session altered.

SQL> select c1 , c2 from t where c1 between to_date('02-AUG-2020 08:00','DD-MON-YYYY HH24:MI')
and to_date('02-SEP-2020 08:00','DD-MON-YYYY HH24:MI') ;  2

C1                C2
----------------- -----------------
01-SEP-2020 11:00 01-SEP-2020 17:00

SQL>

If the original fields are dates, you might change your NLS date settings to make it work, as well as applying to_date to the strings you want to compare as dates

Update

I remove the to_char to avoid an implicit conversion.

Upvotes: 1

EdStevens
EdStevens

Reputation: 3872

Yes, something is different between your two setups. It is the value of NLS_DATE_FORMAT.

Your query is depending on an implicit TO_DATE() to convert the strings '04-Aug-2020 07:00' AND '05-Aug-2020 07:00' from strings to DATE, which is an internal, binary data type. Since you are relying on implicit conversion, you are also relying on the underlying value of NLS_DATE_FORMAT to match the strings you are supplying. This is why I always, always, always use to_date() and include the proper format mask:

RUN_START_DATE between to_date('04-Aug-2020 07:00','dd-Mon-yyyy hh24:mi:ss') AND to_date('05-Aug-2020 07:00','dd-Mon-yyyy hh24:mi:ss')

For more background, see https://edstevensdba.wordpress.com/2011/04/07/nls_date_format/

Upvotes: 1

Related Questions