select DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )
as day from bd_psnbasdoc as bd_psnbasdoc
where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0) 我执行上述语句,提示“消息 241,级别 16,状态 1,第 1 行
从字符串向 datetime 转换时失败。
”错误。
把where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0)
删除后可以执行,如果我想实现把 “day ”实现大于等于0效果,如何实现?请高人指点,谢谢!
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )
as day from bd_psnbasdoc as bd_psnbasdoc
where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0) 我执行上述语句,提示“消息 241,级别 16,状态 1,第 1 行
从字符串向 datetime 转换时失败。
”错误。
把where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0)
删除后可以执行,如果我想实现把 “day ”实现大于等于0效果,如何实现?请高人指点,谢谢!
LEFT(MM,bd_psnbasdoc.birthdate)
LEFT(DD,bd_psnbasdoc.birthdate)
cast((substring('2009-06-25',1,4) + substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2)) as datetime)
DATENAME(MM,bd_psnbasdoc.birthdate)
DATENAME(DD,bd_psnbasdoc.birthdate)
--再嵌套一次
select * from (select DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )
as day from bd_psnbasdoc as bd_psnbasdoc ) a where a.day>=0
from bd_psnbasdoc as bd_psnbasdoc
where DATEDIFF(dd,'2009-06-25',cast(datepart(yy,'2009-06-25')+datepart(mm,bd_psnbasdoc.birthdate),datepart(dd,bd_psnbasdoc.birthdate) as datetime))>=0试试
把你的(substring('2009-06-25',1,4) + substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))换成
cast(datepart(yy,'2009-06-25')+datepart(mm,bd_psnbasdoc.birthdate),datepart(dd,bd_psnbasdoc.birthdate) as datetime)试下
birthdate是生日,我想实现生日预警目的:
取出所有人的生日日期,把年份改为今年,月份和日和生日相同,然后和今天的日期比较,如果能大于0小于45天就显示出来该人员的信息
birthdate的数据如下:
1971-09-29
1987-11-29
1967-04-15
1944-11-28
1962-10-28
1954-07-20
1967-04-15
1984-02-01
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
1985-08-21
1985-11-18
1983-12-31
1984-01-29
1985-09-15
1977-10-02
1984-03-07
1982-11-06
1981-04-25
1986-11-14
1982-01-21
1988-11-12
1973-02-22
1988-11-18
1989-10-13
1988-01-11
1984-04-20
1986-02-20
1981-03-10
1988-04-02
1989-11-17
1986-09-18
1989-01-29
1980-05-27
1989-02-28
1973-11-12
1965-05-05
1967-06-20
1980-05-27
1981-01-02
1981-01-02
NULL
1972-10-27
1981-07-04
1978-07-04
1978-07-04
1986-06-06
1975-07-05
1970-06-06
1960-10-01
1987-02-20
1982-02-12
1984-08-02
1985-03-06
1990-04-01
1986-02-26
1984-10-14
1988-03-12
1954-01-07
1977-03-24
1987-01-17
1980-04-16
1981-02-11
1975-05-13
1981-11-20
1970-10-25
1981-09-02
1973-09-09
1982-05-22
1986-10-06
1981-12-17
1982-06-01
1986-04-15
1987-10-15
1987-05-17
1983-06-30
1986-01-08
1980-06-10
1986-10-10
1985-03-21
1988-04-03
1986-09-21
1986-07-18
1986-11-30
1981-11-24
1988-09-08
1978-03-25
1984-02-10
1988-02-15
1987-02-08
1979-09-14
1981-11-21
1969-09-21
1973-09-27
1985-08-07
1985-03-04
1985-02-10
1981-06-28
1983-02-24
1980-10-18
1970-11-12
1983-08-27
1986-07-14
1947-04-14
1958-08-18
1963-12-29
1976-07-31
1987-12-18
1987-08-26
NULL
1962-12-28
1969-04-04
1985-11-17
1989-07-31
1964-10-30
1961-02-21
1984-10-04
1981-10-28
我写的语句:
select DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )
as day from bd_psnbasdoc as bd_psnbasdoc
where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0)
and (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )<=45)
出现提示错误:消息241,级别16,状态1,第1 行
从字符串向datetime 转换时失败。
表结构如下:
birthdate(char(10),null)
insert @t
select '1983-08-27' union all
select '1986-07-14' union all
select '1947-04-14' union all
select '1958-08-18' union all
select '1963-12-29' union all
select NULL
select id,birthdate,DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )
as day
from @t as bd_psnbasdoc
where (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) )>=0)
and (DATEDIFF(dd,'2009-06-25',convert(datetime,(substring('2009-06-25',1,4) +
substring(bd_psnbasdoc.birthdate,6,2) + substring(bd_psnbasdoc.birthdate,9,2))) ) <=45) id birthdate day
----------- ---------- -----------
2 1986-07-14 19(1 行受影响)
insert @t
select '1983-08-27' union all
select '1986-07-14' union all
select '1947-04-14' union all
select '1958-08-18' union all
select '1963-12-29' union all
select NULL
select id,birthdate,datediff(dd,getdate(),dateadd(yy,datediff(yy,birthdate,getdate()),birthdate)) day
from @t
where datediff(dd,getdate(),dateadd(yy,datediff(yy,birthdate,getdate()),birthdate))>=0
and datediff(dd,getdate(),dateadd(yy,datediff(yy,birthdate,getdate()),birthdate))<=45id birthdate day
----------- ---------- -----------
2 1986-07-14 18
--哈哈已经26号了,day减了一天
(1 行受影响)