-3

i was write a mysql query to get the data between the two dates(ex: 29-05-2021 to 08-06-2021). but it shows the only current month(june) data only. it didn't show the (may) month data.

example:

i was stored john at 30-05-2021, sam at 31-05-2021, raja at 07-06-2021, ramesh at 05-06-2021. When i was run the query only showed raja, ramesh. it's not show the john and sam.

this is my mysql query

SELECT staff_name FROM staff_info WHERE join_date <= '09-06-2021'

in database i was stored date in varchar method and format is d-m-Y.

can any one help to solve these problem.

ADyson
  • 57,178
  • 14
  • 51
  • 63
jack
  • 15
  • 4
  • 3
    Dates should always be stored in a DATE datatype, then you dont get these issues – RiggsFolly Jun 10 '21 at 12:18
  • 1
    `in database i was stored date in varchar method`...well that's your first mistake. Dates are not strings (except when you are showing them to human beings). Use the `date` column type and your life will immediately get a lot easier. – ADyson Jun 10 '21 at 12:18
  • 1
    Now you will have to convert your "bit of unknown text" into a proper date using [`str_to_date()`](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date) So that proper date processing can take place – RiggsFolly Jun 10 '21 at 12:20

1 Answers1

1

You are going to have to convert the text which only you know is a date to a date type so MySQL knows how it can process it easily as a date

str_to_date() is used to convert text into a Date type

SELECT staff_name 
FROM staff_info 
WHERE str_to_date(join_date, '%m/%d/%Y') between '2021-05-31' AND '2021-06-06'
RiggsFolly
  • 93,638
  • 21
  • 103
  • 149