select [name], datediff(d,(left(CONVERT(varchar(100), GETDATE(), 23),4)+right(日期,len(日期)-4)),getdate()) from tb
select [name], datepart(dy,getdate())-datepart(dy,日期) from tb
--> 测试数据:tab if object_id('tab') is not null drop table tab---->建表 create table tab([name] varchar(4),[bir] datetime,[gz] int) insert tab select '赵一','1999-01-01 00:00:00.000',4248 union all select '钱一','1996-02-29 00:00:00.000',5285 union all select '孙一','1934-03-01 00:00:00.000',27930 union all select '李一','1966-04-01 00:00:00.000',16211 union all select '周一','1997-05-01 00:00:00.000',4858 union all select '吴一','1922-11-21 00:00:00.000',32048 union all select '郑一','1989-12-11 00:00:00.000',7556 union all select '王二','1979-08-20 00:00:00.000',11322 union all select '冯一','1982-09-01 00:00:00.000',10214 union all select '陈三','1983-09-06 00:00:00.000',9844select *,距离生日还有= DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate()) from tab where DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate()) between -30 and 0 order by datepart(m,bir),datepart(d,bir) --> 查询结果 SELECT * FROM tab --> 删除表格 --DROP TABLE tab select top 10 * from ( SELECT name,bir ,MONTH(bir),DAY(bir), dd=DATEDIFF(DD,bir,GETDATE()) FROM tab) temp order by dd -------------- name bir gz 距离生日还有 王二 1979-08-20 00:00:00.000 11322 -1 冯一 1982-09-01 00:00:00.000 10214 -13 陈三 1983-09-06 00:00:00.000 9844 -18
between -30 and 0 根据你的需要,修改这个区间值,如果是10天 改为between -10 and 0
select *,距离生日还有= abs(DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate())) from tab where DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate()) between -10 and 0 order by datepart(m,bir),datepart(d,bir) 加了个abs 取正数
select [name], datediff(d,(left(CONVERT(varchar(100), GETDATE(), 23),4)+right(日期,len(日期)-4)),getdate()) from tb
select [name], datepart(dy,getdate())-datepart(dy,日期) from tb
if object_id('tab') is not null
drop table tab---->建表
create table tab([name] varchar(4),[bir] datetime,[gz] int)
insert tab
select '赵一','1999-01-01 00:00:00.000',4248 union all
select '钱一','1996-02-29 00:00:00.000',5285 union all
select '孙一','1934-03-01 00:00:00.000',27930 union all
select '李一','1966-04-01 00:00:00.000',16211 union all
select '周一','1997-05-01 00:00:00.000',4858 union all
select '吴一','1922-11-21 00:00:00.000',32048 union all
select '郑一','1989-12-11 00:00:00.000',7556 union all
select '王二','1979-08-20 00:00:00.000',11322 union all
select '冯一','1982-09-01 00:00:00.000',10214 union all
select '陈三','1983-09-06 00:00:00.000',9844select *,距离生日还有= DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate())
from tab
where DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate()) between -30 and 0
order by datepart(m,bir),datepart(d,bir)
--> 查询结果
SELECT *
FROM tab
--> 删除表格
--DROP TABLE tab
select top 10 * from (
SELECT name,bir ,MONTH(bir),DAY(bir),
dd=DATEDIFF(DD,bir,GETDATE())
FROM tab) temp order by dd
--------------
name bir gz 距离生日还有
王二 1979-08-20 00:00:00.000 11322 -1
冯一 1982-09-01 00:00:00.000 10214 -13
陈三 1983-09-06 00:00:00.000 9844 -18
改为between -10 and 0
from tab
where DateDiff(Day,DateAdd(year,DateDiff(year,bir,GetDate()),bir),GetDate()) between -10 and 0
order by datepart(m,bir),datepart(d,bir)
加了个abs 取正数