CODEWITHSUNDEEP
mysqlsql
craigmiller160
craigmiller160

Reputation: 6273

SQL: Date comparison only comparing years

Ok, so I can't figure out why this is happening. I'm using the following query to generate a list of dates. Essentially, it uses joins and unions to create a list of 100,000 dates (A bit overkill, I know, but that's another thing for later).

At the end of it all, I try to use a WHERE clause to filter the list. So instead of all 10,000, only the dates that fit the criteria will be returned. This is a super fast way to generate a specific list of dates for my program.

My problem is that my where clause fails to make the proper comparison. It returns all dates that are <= the YEAR in the end_date, when I want it to return all dates that are <= the WHOLE DATE in the end_date.

set @start_date = '2015-9-20';
set @end_date = '2016-1-17';

select * from
(select  @start_date + interval ((a.a) + (10 * b.a) + (100 * c.a) + (1000 * d.a) + (10000 * e.a)) day this_date
from 
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as a 
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as b 
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as c
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as d
join
(select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9) as e) v
where this_date <= @end_date;

I can't figure out why this is happening. Everything else works perfectly, the table generation, even other kinds of comparisons (example: dayofweek(this_date) = 1 successfully returns all sundays). But when I include that particular where clause, it returns all dates less than 2016-12-31, where it should cut off at 2016-1-17.

Any help would be appreciated.

EDIT:

So the replies solved my problem, thanks. This is just a temp representation of this, I'm ultimately going to have it be a function accepting date parameters rather than setting variables at the beginning, once I get it worked out.

My last question is on repetition. The end goal of this is to filter the giant list down to specific dates for a repeating event. So for example, the event is every Thursday for a set period, I would use the where clause to narrow down to that.

I already have it working for a weekly repeat like that.

where dayofweek(this_date) = 5

But what I haven't been able to figure out is how to do bi-weekly or monthly, which is what I ultimately want.

I want to do that w this formula for lower overhead than using a loop and dateadd. Just not sure how to do the proper filter.

Upvotes: 0

Views: 98

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

In your first lines you convert your string into a date this_date

 @start_date + interval ....

But in your where you have a problem because are comparing text with dates.
Right now you have:

where this_date <=  '2016-1-17'

You need something like

where this_date <= STR_TO_DATE(@end_date, '%Y-%m-%d')

or

where this_date <=  DATE_ADD('2016-1-17',INTERVAL 0 DAY) ;
where this_date <=  DATE_ADD(@end_date ,INTERVAL 0 DAY) ;

Upvotes: 1

Related Questions