要写一条求离生日还有多少钱的SQL语句,用datediff和datepart搞了成天都不行,请高手赐教。
数据库:employee
id int not null,
name nvarchar (10) not null,
birthday smalldatetime求列出离生日还有一个月的所有员工。谢谢。
数据库:employee
id int not null,
name nvarchar (10) not null,
birthday smalldatetime求列出离生日还有一个月的所有员工。谢谢。
解决方案 »
- sqlserver怎么才能安装上啊!!
- 这样一个简单的SQL排序问题,小弟不才,请各位指教?
- 给出各种简单的查询方法
- 关于一SQL语句 问题
- 如何将字段中的html代码统一替换掉
- 对2个表的联合查询,其中一个用来计算计算另一个表中的字符串数,急,急,请高手指教,谢谢!
- sql server 2005 全文索引 性能问题
- How to save JPG picture to SQL Server with bcb code ?
- 各位高人帮忙看一下这个存储过程在delphi中为什么不好使
- 如何在MSSQL中根据其他表来更新本表新增字段
- 各位大哥大姐好:有谁能提供一个SQL-server2005企业版的下载地址!要好用的!!谢谢了!!!
- SQL报表ReportingService
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT ,DATE DATETIME)
INSERT INTO TB
SELECT 1,'2009-10-01' UNION ALL
SELECT 2,'2009-8-03' UNION ALL
SELECT 3,'2009-10-10'
SELECT * FROM TB T1
WHERE DATEDIFF(DAY,DATE,GETDATE()) BETWEEN 0 AND 31
/*
31 2 2009-08-03 00:00:00.000
*/
这样?
declare @t table(id int identity(1,1),出生日期 datetime)
insert into @t(出生日期)
select '1985-11-23'
union all select '1977-11-24'
union all select '1978-11-25'
union all select '1979-11-26'
union all select '1987-11-27'
union all select '1999-11-28'
union all select '1999-11-29'
union all select '1999-11-30'
select * from(
select *,序号=datediff(day,getdate(),dateadd(year,year(getdate())-year(出生日期),出生日期))
from @t) a
datediff(dd,DATEADD(Year,DATEDIFF(Year,birthday ,getdate()),birthday),getdate())<=30
declare @t table(id int identity(1,1),出生日期 datetime)
insert into @t(出生日期)
select '1985-09-3'
union all select '1977-09-04'
union all select '1978-11-25'
union all select '1979-11-26'
union all select '1987-11-27'
union all select '1999-11-28'
union all select '1999-11-29'
union all select '1999-11-30'
select * from(
select *,天数=datediff(day,getdate(),dateadd(year,year(getdate())-year(出生日期),出生日期))
from @t) a
/*
id 出生日期 天数
----------- ----------------------- -----------
1 1985-09-03 00:00:00.000 0
2 1977-09-04 00:00:00.000 1
3 1978-11-25 00:00:00.000 83
4 1979-11-26 00:00:00.000 84
5 1987-11-27 00:00:00.000 85
6 1999-11-28 00:00:00.000 86
7 1999-11-29 00:00:00.000 87
8 1999-11-30 00:00:00.000 88(8 行受影响)*/
select datediff(day,getdate(),dateadd(year,2,'2007-09-05'))-----------
2(1 行受影响)
where 天数<=30不是球本月过生日的吧?
--借小爱数据 declare @t table(id int identity(1,1),出生日期 datetime)
insert into @t(出生日期)
select '1985-09-3'
union all select '1977-09-04'
union all select '1978-11-25'
union all select '1979-11-26'
union all select '1987-11-27'
union all select '1999-11-28'
union all select '1999-11-29'
union all select '1999-11-30' select datediff(day,getdate(),dateadd(year,datediff(year,出生日期,getdate()),出生日期)) from @t-----------
0
1
83
84
85
86
87
88(8 行受影响)
declare @t table(id int identity(1,1),出生日期 datetime)
insert into @t(出生日期)
select '1985-09-3'
union all select '1977-09-04'
union all select '1978-9-25'
union all select '1979-10-01'
union all select '1987-10-04'
union all select '1999-8-28'
union all select '1999-11-29'
union all select '1999-11-30'
select * from(
select *,月=datediff(mm,getdate(),dateadd(year,year(getdate())-year(出生日期),出生日期))
from @t) a
where 月 = 0 or 月 = 1
/*
id 出生日期 月
----------- ----------------------- -----------
1 1985-09-03 00:00:00.000 0
2 1977-09-04 00:00:00.000 0
3 1978-09-25 00:00:00.000 0
4 1979-10-01 00:00:00.000 1
5 1987-10-04 00:00:00.000 1(5 行受影响)*/
SET @STARTTIME=getdate()
DECLARE @ENDTIME DATETIME
SET @ENDTIME=dateadd(mm,1,getdate())
SELECT *
FROM TB
WHERE (DATEADD(YEAR,DATEDIFF(YEAR,BIRTHDAY,@STARTTIME),BIRTHDAY) BETWEEN @STARTTIME
AND CASE WHEN DATEDIFF(YEAR,@STARTTIME,@ENDTIME)=0
THEN @ENDTIME
ELSE DATEADD(YEAR,DATEDIFF(YEAR,'19001231',@STARTTIME),'19001231')
END)
OR (DATEADD(YEAR,DATEDIFF(YEAR,BIRTHDAY,@ENDTIME),BIRTHDAY) BETWEEN
CASE WHEN DATEDIFF(YEAR,@STARTTIME,@ENDTIME)=0
THEN @ENDTIME
ELSE DATEADD(YEAR,DATEDIFF(YEAR,'19000101',@ENDTIME),'19000101')
END AND @ENDTIME)
建议都看看邹老大的书先吧“中文版SQL SERVER 2000开发与管理应用实例”
csdn 就有得下
特别是关于时间、字符串的问题
看了可以减少提问、提高提问质量我现在就看,虽然还不能快速地帮大家解答
但起码自己少了许多疑问