How to Solve 01810. 00000 - "format code appears twice"

Question

I am new to sql.I am trying to get the records between two dates. But i am getting ORA-01810 error

Query:

SELECT txnid as txnid,
       to_date(txn_date,'YYYY-MM-DD HH24:MI:SS') as txn_date, 
       amount as amount 
  FROM transactionsdata ct 
 where ct.txn_date >= TO_TIMESTAMP('2018-07-01 00:00:00','yyyy-MM-dd HH:mm:ss') 
   and ct.txn_date <= TO_TIMESTAMP('2018-07-27 00:00:00','yyyy-MM-dd HH:mm:ss');

What is wrong in my code?

This is the my table date format: 11-07-18 01:05:17.395000000 PM

Answer 1

Try using this code:

SELECT txnid as txnid,
       to_char(txn_date,'YYYY-MM-DD HH24:MI:SS') as txn_date, 
       amount as amount 
  FROM transactionsdata ct 
   and ct.txn_date >= TO_TIMESTAMP('2018-07-01 00:00:00','yyyy-MM-dd HH24:mi:ss') 
   and ct.txn_date <= TO_TIMESTAMP('2018-07-27 00:00:00','yyyy-MM-dd HH24:mi:ss')

To represent minutes you have to use mi. You used it in your line 2 of code, bit not later.