asked on

Get a date range with mysql

This is the query:

SELECT DATE(FROM_UNIXTIME(@unix_date_time:=substring_index(meta_value, '|',1))) AS "date" FROM wp_postmeta WHERE meta_key LIKE "etimeclockwp-away_%"

Open in new window

it shows a table called date it currently show all dates
2019-11-14
2019-12-02
2019-12-16
2019-11-25
2020-01-08
I have been trying to sort date range using some stack overflow example(

SELECT DATE(FROM_UNIXTIME(@unix_date_time:=substring_index(meta_value, '|',1))) AS "date" FROM wp_postmeta WHERE "date" >=`2019-11-14` AND "date" <=`2019-12-16` meta_key LIKE "etimeclockwp-away_%"

Open in new window

But it did not work.
What am I missing?

Goal = get any date range

Norie

Which dates do you want to select?

Your example query will only return records where the date is 3rd January 2016 and there are no records with that date in your sample data.

Web IT

ASKER

I updated the code in the question to reflect the column.

Norie

As far as I know you can't use aliases in the WHERE clause in MySQL.

So try replacing "date" with meta_value.

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Web IT

ASKER

Thank you very much you might answered one question ,but this answer helped a ton of people get salary reports faster an in an easier way from a wired way to create a time clock.
Keep it up.