0

I have a query that supplies the SYSDATE and a hardcoded time value coming from a configuration file.

select request_rrn||','||transfer_id||','||
client_id||','||src_fund_acct_id||','||
tgt_fund_acct_id||','||
to_char((timestamp + 8/24), 'MON-DD-YYYY HH24:MI:SS')||','||
txn_amount||','||se_respcode
from mi3_txn_logs 
where client_id = 'test'
and (timestamp + 8/24) between to_date(trunc((SYSDATE + 8/24) - 7)||' 08:00:00', 'MM/DD/YYYY HH24:MI:SS')
and to_date(trunc((SYSDATE + 8/24) - 0)||' 07:59:59', 'MM/DD/YYYY HH24:MI:SS')
order by logtime desc;

When I execute this query in sqldeveloper, it works fine. (I think). But when I run this in sqlplus, I get this error

and to_date(trunc((SYSDATE + 8/24) - 0)||' 07:59:59', 'MM/DD/YYYY HH24:MI:SS')
                                        *
ERROR at line 10:
ORA-01858: a non-numeric character was found where a numeric was expected

This is the complete SQL file that I execute through SQLPLUS:

SET TRIMSPOOL ON;
SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF PAGESIZE 50000 LINESIZE 238 WRAP OFF;
SPOOL /home/ec2-user/out.csv;
select request_rrn||','||transfer_id||','||
client_id||','||src_fund_acct_id||','||
tgt_fund_acct_id||','||
to_char((timestamp + 8/24), 'MON-DD-YYYY HH24:MI:SS')||','||
txn_amount||','||se_respcode
from mi3_txn_logs 
where client_id = 'test'
and (timestamp + 8/24) between to_date(trunc((SYSDATE + 8/24) - 1)||' 08:00:00', 'MM/DD/YYYY HH24:MI:SS')
and to_date(trunc((SYSDATE + 8/24) - 0)||' 07:59:59', 'MM/DD/YYYY HH24:MI:SS')
order by logtime desc;
SPOOL OFF;
quit

I think I'm having issue with my SYSDATE and hardcoded time concatenation. Any ideas?

makalshrek
  • 853
  • 3
  • 14
  • 29

1 Answers1

1

You are nesting implicit data type conversions here.

The part to_date(trunc((SYSDATE + 8/24) - 0) will convert the result of trunc((SYSDATE + 8/24) - which is a DATE - into a VARCHAR using the current NLS settings and then convert that VARCHAR back into a DATE - which it was to begin with.

You need to convert the "truncated" date into a correctly formatted VARCHAR and then apply the to_date() on the result of the concatenation.

to_date(to_char(trunc(SYSDATE + 8/24), 'MM/DD/YYYY')||' 07:59:59', 'MM/DD/YYYY HH24:MI:SS')

(I removed the - 0 to reduce the noise in the expression and cut down on parentheses)

You need to apply this to the other expression as well

Unrelated, but: I prefer using a proper interval for better readability:

timestamp + interval '8' hour instead of timestamp + 8/24