Fetch data between two dates

Question

I want to know how to fetch data between two dates using SQL queries. In my table date format is dd/mm/yyyy hh:mm:ss am/pm.

According to these dates format I need to fetch data. Column is DATE type.

I tried with below query

select * from sales 
where to_char(txn_date, 'dd-mm-yyyy' ) 
between '10-09-2018' and '30-09-2018'

I am getting wrong records. Why?

Which database are you using (e.g. MySQL, Oracle, SQL Server)? What is the type of your date column? Have you tried a query yet? — Tim Biegeleisen, Nov 16 '18 at 06:01
@MitchWheat I wouldn't presume that this means the column is text (though it could be). I won't counter your downvote, however, due to no effort being shown. — Tim Biegeleisen, Nov 16 '18 at 06:02
@ Tim Biegeleisen : pretty difficult to have a format if it's not a string.... — Mitch Wheat, Nov 16 '18 at 06:03
Possible duplicate of [Select data from date range between two dates](https://stackoverflow.com/questions/14208958/select-data-from-date-range-between-two-dates) — Alexis Villar, Nov 16 '18 at 06:03
@Tim....coloumn is date type and I tried with below queries "select *from sales where to_char(txn_date, 'dd-mm-yyyy' ) between 10-09-2018 and 30-09-2018" — Rakesh Divedi, Nov 16 '18 at 06:26
Please post some sample data which reproduces the problem, and an indication of the records you expect it to return. — APC, Nov 16 '18 at 07:04
For future reference, please **edit your question** to provide clarification or additional information. In particular, code is hard to read in comments due to poor formatting. — APC, Nov 16 '18 at 07:14
@RakeshDivedi If you have got an answer to your question, please accept the answer — MicBehrens, Nov 20 '18 at 10:54

MicBehrens · Answer 1 · 2018-11-16T19:38:11.330

It looks like you're saving the timestamps as TEXT or VARCHAR in the database. If that is the case, you can't. And it's a really bad approach. Please use type DATETIME instead, which enables you to use the BETWEEN operator.

When you're using the DATETIME type, you can do

SELECT *
FROM sales
WHERE txn_date BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59'

Which will give you all the data in the year of 2018 and still using your indexes

EDIT

Since you've now told that you're using DATE as data type, you can use the same solution, without the time.

SELECT *
FROM sales
WHERE txn_date BETWEEN '2018-01-01' AND '2018-12-31'

In table txn_date has date data type – Rakesh Divedi Nov 16 '18 at 06:43 — Rakesh Divedi, Nov 16 '18 at 06:43

APC · Answer 2 · 2018-11-16T07:18:37.827

You are getting wrong records because you are converting the dates into a string. That means the query uses string semantics when doing the comparison. As a string, '15-12-2999' and '15-05-2000 are between '10-09-2018' and '30-09-2018' because '15' has a higher ASCII value than '10' and a lower ASCII value than '30'.

The solution is quite simple: use date datatypes for the comparison.

select * from sales 
where txn_date between date '2018-09-10' and date '2018-09-30'

If TXN_DATE has a time element you may want to change the query to

select * from sales 
where txn_date >= date '2018-09-10' 
and txn_date < date '2018-10-01'

Otherwise you will not retrieve records for transactions made after midnight on 30th September.

Alternatively you could remove the time element by truncating the date:

select * from sales 
where trunc(txn_date) between date '2018-09-10' and date '2018-09-30'

score 0 · Answer 3 · answered Nov 16 '18 at 19:40

0

select * from sales where trunc(txn_date ) between '10-09-2018' and '30-09-2018' If you have the format date/time trunc would eliminate the time in the date

answered Nov 16 '18 at 19:40

Himanshu

3,830
2
10
29

score 0 · Answer 4 · answered Dec 12 '18 at 07:44

0

Finally I got answer for this question Select *from sales where txn_date between to_date('01-10-2018 00:00:00','DD-MM-YYYY HH24:MI:SS') And to_date('31-10-2018 23:59:59','DD-MM-YYYY HH24:MI:SS')

answered Dec 12 '18 at 07:44

Rakesh Divedi

21
3

Fetch data between two dates

4 Answers4