Question

在我的表格中,我有两个独立的日期字段,X和Y。在Fieldx中,我的日期是2008年8月13日,在FieldY中,我的日期是2015年8月13日。

我想知道是否有一种Sql方法来计算出在X和Y字段之间的6个月度期间,结果显示在不同的线条上吗?所以,我希望在两个领域得出以下结果:

BEGIN     | END
13-Aug-08 | 13-Feb-09
13-Feb-09 | 13-Aug-09
13-Aug-09 | 15-Feb-10
15-Feb-10 | 13-Aug-10
13-Aug-10 | 14-Feb-11
14-Feb-11 | 15-Aug-11
15-Aug-11 | 13-Feb-12
13-Feb-12 | 13-Aug-12
13-Aug-12 | 13-Feb-13
13-Feb-13 | 13-Aug-13
13-Aug-13 | 13-Feb-14
13-Feb-14 | 13-Aug-14
13-Aug-14 | 13-Feb-15
13-Feb-15 | 13-Aug-15

有可能吗?

谢谢谢谢

Answer 1

你可以尝试这样的东西:

DATE  2006-05-20  + INTERVAL 5 DAY   -> DATE  2006-05-25  
DATE  2006-05-20  + INTERVAL 5 MONTH -> DATE  2006-10-20  
DATE  2006-05-20  + INTERVAL 5 YEAR  -> DATE  2011-05-20

<强 > 示例1:

CREATE TABLE BEGIN_DATES
(
     BDates DATETIME
); 

INSERT INTO BEGIN_DATES VALUE ( 13/08/2008 ); 
INSERT INTO BEGIN_DATES VALUE ( 13/02/2009 );
INSERT INTO BEGIN_DATES VALUE ( 13/08/2009 );
INSERT INTO BEGIN_DATES VALUE ( 15/02/2010 );
INSERT INTO BEGIN_DATES VALUE ( 13/08/2010 );
INSERT INTO BEGIN_DATES VALUE ( 14/02/2011 );
INSERT INTO BEGIN_DATES VALUE ( 15/08/2011 );
INSERT INTO BEGIN_DATES VALUE ( 13/02/2012 );
INSERT INTO BEGIN_DATES VALUE ( 13/08/2012 );
INSERT INTO BEGIN_DATES VALUE ( 13/02/2013 );
INSERT INTO BEGIN_DATES VALUE ( 13/08/2013 );
INSERT INTO BEGIN_DATES VALUE ( 13/02/2014 );
INSERT INTO BEGIN_DATES VALUE ( 13/08/2014 );
INSERT INTO BEGIN_DATES VALUE ( 13/02/2015 );


SELECT BDates AS BEGIN, (BDates + INTERVAL 6 MONTH) AS END
FROM BEGIN_DATES

DROP TABLE BEGIN_DATES

<强度 > 2 :

BEGIN
SET @date =  2008-08-13 ;
SET @Edate = 2015-08-13 ;

WHILE @date <= @Edate DO

SELECT @date AS BEGIN_DATE, CAST(@date AS DATETIME) + INTERVAL 6 MONTH AS END_DATE;  -- ADDDATE(@date,INTERVAL 6 MONTH)

SET @date = CAST(@date AS DATETIME) + INTERVAL 6 MONTH;   -- Or ADDDATE(@date,INTERVAL 6 MONTH)

END WHILE;
END

友情链接