CODEWITHSUNDEEP
mysql
Danilo
Danilo

Reputation: 382

Query that displays rows in a range of date with day, month and year

I have the following columns in my table Log:

year, month, day, info

I need a query that selects the rows in a range of date determined by the user. The user will select the initial day, month and year and also the final day, month and year. At the moment, my query is:

SELECT
    CONCAT(LPAD(`day`,2, 0),'/',LPAD(`month`,2, 0),'/',`year`) AS data,
    info
FROM
    Log
WHERE
    (year > :initial_year OR (year = :initial_year AND month >= :initial_moth AND day >= :initial_day))
    AND (year < :final_year OR (year = :final_year AND month <= :final_month AND day <= :final_day))
GROUP BY
    year, month, day
ORDER BY
    year DESC, month DESC, day DESC

But this query doesn't display any results, even that they are in the database! What is wrong and how can I fix it?

Upvotes: 0

Views: 48

Answers (2)

Arth
Arth

Reputation: 13110

Your logic is wrong:

WHERE (
        year > :initial_year OR (
          year = :initial_year AND month >= :initial_moth AND day >= :initial_day
        )
      )

Will exclude any dates in your initial year where the day portion is greater than the initial day portion. e.g. yyyy-01-31 as the initial day will exclude all results for yyyy where the day portion is not 31.

Similar problems exist with the final date.

As suggested in the comments, use one DATE field in your database and do the other fiddling in your application code; it will save a lot of drama.

If you can't change the database, find and berate the person who designed it until they change it. If you can't do that then:

WHERE (year>:initial_year OR (year=:initial_year AND (month>:initial_month OR (month=:initial_month AND day>=:initial_day))))

and similar for the final date

Upvotes: 1

Gudfinnur Kristjansson
Gudfinnur Kristjansson

Reputation: 11

What about:

select concat(year,month,day) as thedate, info from log where thedate >= :startdate and thedate <= :enddate order by thedate desc;

Upvotes: 0

Related Questions