我要查询4月5日到8月7日之前过生日的会员该如何写SQL啊、?,,在线等谢谢各位啦。。
解决方案 »
- 存储过程啊,受不了了,大侠帮忙啊
- 急~~~求教SQL行变列的问题
- 在多用户同时insert 的情况下 使用 select MAX(IDENTITYCOL) 来取得 ID 是否可靠??
- 求一条日期同比的sql语句
- Linux 下使用Freetds 连接MSSQL 的问题
- 字符串邊界符??
- C#同时向两个表中插入数据且有关联,该怎样做?
- 在Sql-server中怎么建立一个新的Sql-server组,我建了多次没成功请给详细步骤谢谢!
- 数据库访问断开的问题
- 有关网络数据库的问题
- 存储过程中包含“0x4465636c617265204054205661726368617228323535292c404320566172636861...”
- 有点复杂的查询
where right(convert(char(08),生日栏位,112),4) between '0405' and '0807'
select * from t where substring(convert(varchar(8),birthday,112),5,4) between '0405' and '0807'
INSERT @t SELECT 1,'aa','1999-01-01'
UNION ALL SELECT 2,'bb','1996-02-29'
UNION ALL SELECT 3,'bb','1934-03-01'
UNION ALL SELECT 4,'bb','1966-04-01'
UNION ALL SELECT 5,'bb','1997-05-01'
UNION ALL SELECT 6,'bb','1922-11-21'
UNION ALL SELECT 7,'bb','1989-12-11'DECLARE @dt1 datetime,@dt2 datetime
SELECT @dt1='2003-12-05',@dt2='2006-02-28'
select * from @t
where dateadd(year,datediff(year,birthday,@dt1),birthday) between @dt1 and convert(datetime,convert(char(5),@dt1,120)+'12-31')
or dateadd(year,datediff(year,birthday,@dt1)+1,birthday) between convert(datetime,convert(char(5),dateadd(year,1,@dt1),120)+'1-1')and @dt2
where cast('1900'+right(convert(varchar(10),生日字段,23),6) as datetime)
between '1900-04-05' and '1900-08-07'
(
ID int,
[Name] varchar(10),
Birthday datetime
)
insert into #Birthday select 1,'aa','1999-01-01'
union all select 2,'bb','1996-02-29'
union all select 3,'cc','1993-03-01'
union all select 4,'dd','1966-04-01'
union all select 5,'ee','1997-05-01'
union all select 6,'ff','1922-11-21'
union all select 7,'gg','1989-12-11'
--查询2003-12-05到2004-02-28之间的生日
declare @StartTime datetime
set @StartTime='2003-12-05'
declare @EndTime datetime
set @EndTime='2004-02-28'
select * from #Birthday 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,Birthday,@EndTime)=0 then @EndTime
else dateadd(year,datediff(year,'1900-01-01',@EndTime),'1900-01-01') end and @EndTime
where right(convert(varchar(10),Birthday,120),5)
between '04-05' and '08-07' 其实这样就行了。
declare @StartTime datetime
set @StartTime='2003-12-05'
declare @EndTime datetime
set @EndTime='2004-02-28'
select * from #Birthday 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,'1900-01-01',@EndTime),'1900-01-01') end and @EndTime
--需要考虑跨年
declare @b char(04),@e char(04)
select @b='0405',@e='0807'select * from T
where ((right(convert(char(08),生日栏位,112),4) between @b and @e) and @b<=@e)
or (@b>@e and (right(convert(char(08),生日栏位,112),4) >=@e or right(convert(char(08),生日栏位,112),4)<=@b))
declare @StartDate datetime --起始时间
declare @EndDate datetime --结束时间
set @StartDate = '2009-4-5'
set @EndDate = '2009-8-7'
select * from [table] where
dateadd(yy,datediff(yy,Birthday,@StartDate),birthday) between @StartDate and @EndDate+1
or dateadd(yy,datediff(yy,Birthday,@EndDate),birthday) between @StartDate and @EndDate+1起始时间要小于于结束时间,分别对起始时间和结束时间操作,解决了跨年的问题,结束时间加1是按完整天数技术,包括时分秒