SQL:日期比较仅比较年份 [英] SQL: Date comparison only comparing years

问题描述

好吧，我不明白为什么会这样.我正在使用以下查询生成日期列表.本质上，它使用连接和联合来创建一个包含 100,000 个日期的列表(我知道这有点矫枉过正，但那是另一回事了).

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).

最后，我尝试使用 WHERE 子句来过滤列表.因此，不会返回所有 10,000 个，而是仅返回符合条件的日期.这是为我的程序生成特定日期列表的超快速方法.

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.

我的问题是我的 where 子句无法进行正确的比较.它返回所有在 end_date 中 <= YEAR 的日期，当我希望它返回所有在 end_date 中 <= WHOLE DATE 的日期时.

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;

我不明白为什么会这样.其他一切正常，表格生成，甚至其他类型的比较(例如:dayofweek(this_date) = 1 成功返回所有星期日).但是当我包含那个特定的 where 子句时，它返回所有小于 2016-12-31 的日期，它应该在 2016-1-17 截止.

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.

任何帮助将不胜感激.

所以回复解决了我的问题，谢谢.这只是对此的临时表示，一旦我得到解决，我最终会将它变成一个接受日期参数的函数，而不是在开始时设置变量.

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.

我的最后一个问题是关于重复.这样做的最终目标是将巨型列表过滤到重复事件的特定日期.因此，例如，事件在固定时间段内是每周四，我将使用 where 子句来缩小范围.

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.

我想用这个公式来实现比使用循环和 dateadd 更低的开销.只是不确定如何进行适当的过滤.

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.

推荐答案

在您的第一行中，您将字符串转换为日期 this_date

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

 @start_date + interval ....

但是在您的 where 中，您遇到了问题，因为将文本与日期进行比较.
现在你有:

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

where this_date <=  '2016-1-17'

你需要类似的东西

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

或

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

这篇关于SQL:日期比较仅比较年份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！