已知 月份和日期
起始月份字符串是 06-01 结束字符串是 07-01表中存在数据 字段mbirthday: 1980-06-06 00:00:00
求 sql语句中 where语句 如何写 能得到 字段mbirthday 是否过生日
--为什么这样是错的呢
select mbirthday from TmMember where
mbirthday<convert(varchar(12),year(mbirthday)+'-06-10',112)
and
mbirthday>convert(varchar(12),year(mbirthday)+'-05-10',112)
起始月份字符串是 06-01 结束字符串是 07-01表中存在数据 字段mbirthday: 1980-06-06 00:00:00
求 sql语句中 where语句 如何写 能得到 字段mbirthday 是否过生日
--为什么这样是错的呢
select mbirthday from TmMember where
mbirthday<convert(varchar(12),year(mbirthday)+'-06-10',112)
and
mbirthday>convert(varchar(12),year(mbirthday)+'-05-10',112)
--测试数据
DECLARE @t TABLE(ID int,Name varchar(10),Birthday datetime)
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
/*思路:1、把生日的年加到和开始的日期的年相同,即与@dt1的年相同,检查是否在@dt1到该年年底之间
2、把生日的年加到和开始的日期的下一年,即@dt1的下一年,检查是否在@dt1下一年开始到@dt2之间
*/
mbirthday<cast(ltrim(year(mbirthday))+'-06-10' as datetime)
and
mbirthday>cast(ltrim(year(mbirthday))+'-05-10' as datetime)
mbirthday between cast(year(mbirthday)+'-06-10' as datetime) and cast(year(mbirthday)+'-05-10'as datetime)日期和日期比较,你的是日期和字符串比较
select mbirthday from TmMember where
mbirthday<datename(yy,mbirthday)+'-06-10'
and
mbirthday>datename(yy,mbirthday)+'-05-10'
(
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
--为什么 我这样写 就报错呢
cast(year(mbirthday)+'-05-10' as datetime)--加上ltrim就没问题了,
你能数字和字符串相加吗
肯定不行的
因为 year()出来的是int型,int型不能跟字符型直接拼串.