select
CASE WHEN isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) >= 5 THEN 5
ELSE isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) END yearnum,
DATEADD(DD,1, isnull(a.reltodt, b.todt)) backdate,
dateadd(year,CASE WHEN isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) >= 5 THEN 5
ELSE isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) END, DATEADD(DD,1, isnull(a.reltodt, b.todt))) entdt
from bn_person a,bn_info b
where a.info_id=b.id
yearnum backdate entdt
3.000000 2010-02-06 00:00:00.000 2013-02-06 00:00:00.000
3.000000 2010-02-06 00:00:00.000 2013-02-06 00:00:00.000
.500000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000
.500000 2010-05-30 00:00:00.000 2010-05-30 00:00:00.000
.500000 2010-08-30 00:00:00.000 2010-08-30 00:00:00.000
.500000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000
1.500000 2010-06-10 00:00:00.000 2011-06-10 00:00:00.000
3.000000 2010-07-18 00:00:00.000 2013-07-18 00:00:00.000
3.000000 2010-07-18 00:00:00.000 2013-07-18 00:00:00.000为什么当yearnum带有.5 或是1.5时 entdt得到是错的
象
1.500000 2010-06-10 00:00:00.000 2011-06-10 00:00:00.000
2010-06-10 00:00:00.000在1.5年之后得是
entdt 2011-12-10 00:00:00.000 才对啊
CASE WHEN isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) >= 5 THEN 5
ELSE isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) END yearnum,
DATEADD(DD,1, isnull(a.reltodt, b.todt)) backdate,
dateadd(year,CASE WHEN isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) >= 5 THEN 5
ELSE isnull(a.relday, b.daynum) / 15 * 6 / (12 * 1.0) END, DATEADD(DD,1, isnull(a.reltodt, b.todt))) entdt
from bn_person a,bn_info b
where a.info_id=b.id
yearnum backdate entdt
3.000000 2010-02-06 00:00:00.000 2013-02-06 00:00:00.000
3.000000 2010-02-06 00:00:00.000 2013-02-06 00:00:00.000
.500000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000
.500000 2010-05-30 00:00:00.000 2010-05-30 00:00:00.000
.500000 2010-08-30 00:00:00.000 2010-08-30 00:00:00.000
.500000 2010-08-08 00:00:00.000 2010-08-08 00:00:00.000
1.500000 2010-06-10 00:00:00.000 2011-06-10 00:00:00.000
3.000000 2010-07-18 00:00:00.000 2013-07-18 00:00:00.000
3.000000 2010-07-18 00:00:00.000 2013-07-18 00:00:00.000为什么当yearnum带有.5 或是1.5时 entdt得到是错的
象
1.500000 2010-06-10 00:00:00.000 2011-06-10 00:00:00.000
2010-06-10 00:00:00.000在1.5年之后得是
entdt 2011-12-10 00:00:00.000 才对啊
dateadd(m,6,getdate())
用于增加 datepart 的值。这是精确数字或近似数字数据类型类别的表达式,或者是可以隐式转换为 float 类型的表达式。如果指定的值不是整数,将丢弃该值的小数部分。例如,如果为 datepart 指定 day,并为 number 指定 1.75,则 date 将加 1。在线帮助文档的说明我尝试过转换,但是好像不行
select dateadd(yy,cast(0.5 as numeric(5,2)), getdate())或者select dateadd(yy,convert(float,0.5,102),getdate())
所以...
用year 得month