Search Between Date in Query is not showing the correct Result-CodePudding

I'm trying to run a query between dates from Mysql Database and my date format saved in data is dd-mm-yyyy

But I am getting the date of the previous year along with the searched data.

Below is my code

SELECT * FROM `msr_bills` WHERE `bill_date` BETWEEN '15-09-2021' AND '23-09-2021';

CodePudding user response：

Try:

SELECT * FROM `msr_bills` 
WHERE STR_TO_DATE(bill_date, '%d-%m-%Y')
BETWEEN STR_TO_DATE('15-09-2021', '%d-%m-%Y')  AND  STR_TO_DATE('23-09-2021', '%d-%m-%Y') ;

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

When you store data in text format you always should convert it to correct date format

CodePudding user response：

you can try

Select * from msr_bills
WHERE STR_TO_DATE(`bill_date`, '%d-%m-%Y') BETWEEN STR_TO_DATE('15-09-2021', '%d-%m-%Y') AND STR_TO_DATE('23-09-2021', '%d-%m-%Y')

CodePudding user response：

CREATE TABLE `msr_bills` (
  `stuid` int(11) NOT NULL,
  `name` text NOT NULL,
  `address` text NOT NULL,
  `number` text NOT NULL,
  `dob` text NOT NULL,
  `seva` text NOT NULL,
  `amount` text NOT NULL,
  `bill_no` text NOT NULL,
  `bill_date` text NOT NULL,
  `seva_date` text NOT NULL,
  `email` text NOT NULL,
  `remarks` text NOT NULL,
  `unique_id` text NOT NULL,
  `employee_attendant` text NOT NULL,
  `payment_mode` text NOT NULL,
  `gothra` text NOT NULL,
  `rashi` text NOT NULL,
  `nakshatra` text NOT NULL,
  `billing_time` text NOT NULL,
  `anniversary` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;