CODEWITHSUNDEEP
mysqlsql
user2021924
user2021924

Reputation: 1

sql from and to date range

trying to get data between these date ranges but keeps throwing syntax error:

syntax to use near '<='2016-01-06'))

Select user_email
from wp_users
where ID in (
        select user_id
        from wp_usermeta
        where (
                wp_usermeta.meta_key = 'last_login'
                and CAST(wp_usermeta.meta_value As DATE) >= '2000-01-06'
                and <= '2016-01-06'
                )
        )

Upvotes: 0

Views: 42

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You have an unnecessary and <=. You want either between or two comparisons:

Select user_email
from wp_users u
where ID in (select um.user_id
             from wp_usermeta um
             where um.meta_key = 'last_login' and 
                   CAST(um.meta_value As DATE) >= '2000-01-06' and 
                   CAST(um.meta_value As DATE) <= '2016-01-06'
            );

Often, joins perform better than in. If last_login only appears once per user, you might try:

Select u.user_email
from wp_users u join
     wp_usermeta um
     on u.id = um.user_id
where  um.meta_key = 'last_login' and
      CAST(um.meta_value As DATE) >= '2000-01-06' and 
      CAST(um.meta_value As DATE) <= '2016-01-06';

Then, if the date is stored as YYYY-MM-DD, then the cast is unnecessary (comparison as strings works):

Select u.user_email
from wp_users u join
     wp_usermeta um
     on u.id = um.user_id
where um.meta_key = 'last_login' and
      um.meta_value >= '2000-01-06' and 
      um.meta_value <= '2016-01-06';

Finally, this version can take advantage of an index on wp_usermeta(meta_key, meta_value, user_id).

Upvotes: 0

niktrs
niktrs

Reputation: 10066

You forgot the criteria before the second date check 

Select user_email
from wp_users
where ID in (
        select user_id
        from wp_usermeta
        where (
                wp_usermeta.meta_key = 'last_login'
                and CAST(wp_usermeta.meta_value As DATE) >= '2000-01-06'
                and CAST(wp_usermeta.meta_value As DATE) <= '2016-01-06'
                )
        )

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

You're either thinking of using BETWEEN or you're missing a condition for your <= evaluation.

Try:

SELECT user_email
FROM wp_users
WHERE ID IN (
        SELECT user_id
        FROM wp_usermeta
        WHERE (
                wp_usermeta.meta_key = 'last_login'
                AND CAST(wp_usermeta.meta_value AS DATE) >= '2000-01-06'
                AND CAST(wp_usermeta.meta_value AS DATE) <= '2016-01-06'
                )
        )

or

SELECT user_email
FROM wp_users
WHERE ID IN (
        SELECT user_id
        FROM wp_usermeta
        WHERE (
                wp_usermeta.meta_key = 'last_login'
                AND CAST(wp_usermeta.meta_value AS DATE) BETWEEN '2000-01-06'
                    AND '2016-01-06'
                )
        )

Upvotes: 2

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

You are missing CAST(wp_usermeta.meta_value As DATE) before <= '2016-01-06'

Upvotes: 1

Related Questions