CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Nick
Nick

Reputation: 3845

ORACLE 11g SQL date query not returning data for today

I have the following SQL which should return all data up to 6.30am tomorrow. It has been working correctly until today (30/6/2016).

SELECT TO_CHAR(TRUK.THEDATE,'DD/MM/YY HH24:MI') DAT
FROM TRUK
WHERE TO_CHAR(TRUK.THEDATE,'DD/MM/YY HH24:MI') <= TO_CHAR(TO_DATE(sysdate + 1) + 6.5/24,'DD/MM/YY HH24:MI') 
ORDER BY TRUK.THEDATE

PROBLEM Today the data returned does not include data for 30th June,which i know exists but only 1st July. When i comment out the where clause, then all data is returned but of course this includes data AFTER 6.30am for the next day which i don't want returned.

I have searched in vain for an answer and would greatly appreciate some assistance with this. How Could the where clause be written differently to prevent this?

My desired result is that all records in the database are returned up to 6.30am the following day.

cheers

Upvotes: 0

Views: 69

Answers (1)

Rob van Wijk
Rob van Wijk

Reputation: 17705

You're comparing strings, but you should be comparing dates.

Adjust your where clause to this and it'll work:

WHERE truk.thedate <= trunc(sysdate) + to_dsinterval('1 06:30:00')

Upvotes: 3

Related Questions