如何按时段间隔选择日期/对日期进行排序? [英] How I can select / sort dates by period intervals?

问题描述

例如:

如果我们在表中有如下记录:

If we have in table records like:

25/06/2009
28/12/2009
19/02/2010
16/04/2011
20/05/2012

我想根据从当前日期开始的6个月时间间隔来拆分/选择此日期. 结果应该是这样的: 从现在开始0到6个月:第一个记录 从现在开始的7到12个月:第二次记录 ...

I want to split/select this dates according to 6 month intervals starting from current date. result should be like: 0-6 month from now: first record 7-12 month from now: second record ...

如果您将其简化，就像我使它变得非常愚蠢和复杂一样，将会非常感激:

It will be much apreciated if you make this simple as I made it very stupid and complicated like:

declare variable like t1=curdate()+6 
t2=curdate()+12

...

然后选择记录以适合curdate()和t1，然后t1和t2等.

then selected records to fit between curdate() and t1, then t1 and t2 etc.

谢谢

r.

推荐答案

更正:倒退了，需要使用Modulus，而不是整数除法-对不起...

CORRECTION: Had it backwards, Need to use Modulus, not integer division - sorry...

如果MonthCount是一个计算值，用于计算自特定的12月31日以来的月数，并且mod是模数除法(除法后输出余数)

If MonthCount is a calculated value which counts the number of months since a specific Dec 31, and mod is modulus division (output the remainder after dividing)

Select [Column list here] 
From Table
Group By Case When MonthCount Mod 12 < 6 
         Then 0 Else 1 End  

例如，在SQL Server中，您可以使用DateDiff函数

In SQL Server, for example, you could use the DateDiff Function

Select [Column list here] 
From Table
Group By Case When DateDiff(month, myDateColumn, curdate) % 12 < 6 
         Then 0 Else 1 End

(在SQL Server中，百分号是模运算符)

( in SQL Server the percent sign is the modulus operator )

这会将所有记录分组到每个包含六个月数据的存储桶中

This will group all the record into buckets which each contain six months of data

这篇关于如何按时段间隔选择日期/对日期进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！