CODEWITHSUNDEEP
sqldate
ashish_k
ashish_k

Reputation: 1581

Unable to retrieve data for a specific date in SQL

I'm trying to retrieve data for a specific date with query:

Select * from table_1 where activity_date = to_date('13-09-2017','DD-MM-YYYY');

But I'm not getting any result.

But when I query below:

Select * from table_1 where activity_date >= to_date('13-09-2017','DD-MM-YYYY');

I'm getting the data for days greater than equal to 13-09-2017. Any help?

Upvotes: 0

Views: 859

Answers (2)

Maciej Jureczko
Maciej Jureczko

Reputation: 1598

When you compare dates remember that you usually compare some kind of a timestamp. A timestamp usually also has hours, minutes, seconds etc. So in your database you probably have a whole timestamp like '13-09-2017' + specific hour, minutes and so on, whilst to_date explicitly only formats your DD MM and YYYY causing the hour, minutes and seconds to be equal to 0. The query is probably not returning any records as you probably don't have a date with the exact time equal to 00:00:00.

That's also why a comparison like >= will return results. Because your '13-09-2017' database entries are all greater than '13-09-2017' (with hour equal to 00:00:00) because they all have some hours, minutes and seconds to them, for e.g 13-09-2017 04:02:45 which is greater than 13-09-2017 00:00:00).

How to solve this? Well for example:

SELECT * 
FROM table_1 
WHERE activity_date >= to_date('13-09-2017','DD-MM-YYYY') 
  AND activity_date < to_date('14-09-2017','DD-MM-YYYY')

Upvotes: 1

Pranav Prakash
Pranav Prakash

Reputation: 869

It seems the data type of your column is Date Time and hence when you are querying with just date (13-09-2017), it searching for date as well as time (13-09-2017 00:00:00). Seems there are no record in your database with this exact date time stamp and hence no results are being returned. Below query will work in your case:

SELECT *
FROM table_1
WHERE activity_date >= to_date('13-09-2017','DD-MM-YYYY')
  AND activity_date < to_date('14-09-2017','DD-MM-YYYY');

Upvotes: 1

Related Questions