苯办法比较,存储过程 create proc betweenday @bday as varchar(50),@eday as varchar(50) as begin select * from talbe where (year(getdate())+ '-' + month(生日字段) + '-' + day(生日字段)) between year(getdate())+ '-' + @bday and year(getdate())+ '-' + @eday end go sql中调用 exec betweenday '1-10','1-13'
SELECT * FROM TABLENAME WHERE {fn Year(CURDATE())}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()) 说明: CURDATE():SQL命令,取得当前日期 {Fn Year()}:SQL命令,取得时间格式的年部分,{Fn Month()}、Day()类似; DateADD:SQL命令,日期加。
cuizm(射天狼)办法可以,改一下,在ACCESS中通过: ...... Dim sql As String Dim iDate, aftDate
samwzhang() 都没仔细看楼主的回话哦 当天2004-12-31日,生日NN年1月1日 你的就成了 2004年1月1日 between 2004-12-31日 and 2005-1-2日 能行吗。 劝楼主多分几段,按实际情况写几个sql就行了。
1、 SELECT * FROM TABLENAME WHERE ({fn Year(CURDATE())}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE())) or (({fn Year(CURDATE())+1)}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))
2、 SELECT * FROM TABLENAME WHERE (({fn Year(CURDATE())}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))*(Datediff(Day,CURDate(),{fn Year(CURDATE())}+'-12-31')>=2)+(({fn Year(CURDATE())+1)}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))*(Datediff(Day,CURDate(),{fn Year(CURDATE())}+'-12-31')<2) )
一句SQL语句可能不行吧。 试试: Dim sql As String Dim iDate(2) As String Dim iNow, i
iNow = Format(Text1.Text, "MM-DD-YYYY") For i = 0 To 2 iDate(i) = Format(DateAdd("d", i, iNow), "M-D") Next i sql = "select * from tb_birthday " & _ "where (month(birthday) & '-'& day(birthday)) "& _ "in ('" & iDate(0) & "', '" & iDate(1) & "','" & iDate(2) & "')"
居然没有高手来???看我的存储过程: create function GetBirth(@birthdate datetime) returns @birthTable table (client_name varchar(30),birth datetime) as begin if month(@birthdate)=month(@birthdate+2) then insert into @birthtable select client_name,birth from clienttable where month(birth)=month(@birthdate) and day(birth)>=day(@birthdate)and day(birth)<=day(@birthdate+2) else insert into @birthtable select client_name,birth from clienttable where (month(birth)=month(@birthdate) and and day(birth)>=day(@birthdate)) or ((month(birth)=month(@birthdate+2) and day(birth)<=day@birthdate+2)) return end--调用: select * from getbirth('2003-02-28')
if month(getdate())=month(getdate()+2) then select client_name,birth from clienttable where month(birth)=month(getdate()) and day(birth)>=day(getdate())and day(birth)<=day(getdate()+2) else select client_name,birth from clienttable where (month(birth)=month(getdate()) and and day(birth)>=day(getdate())) or ((month(birth)=month(getdate()+2) and day(birth)<=day(getdate()+2))这样也不能提交的吗?
rs.open "select * from talbe where 日期字段 between " & date-1 &" and " & date+1 &" ",cn,1,3
dim datestr as stringdatestr=dtpicker1.valuers.open "select * from talbe where 日期字段 between '" & datestr &"' and '" & datestr+2& "' ",cn,1,3
Dim strSQL As String
curDate = Now
aftDate = DateAdd("d", 2, curDate)
strSQL = "SELECT * FROM TABLENAME WHERE 日期字段 BETWEEN '" & Format(curDate, "YYYY/MM/DD") & "' AND '" & Format(aftDate, "YYYY/MM/DD") & "'"
所以直接用date去比较怕是不行吧,很多都忘了这一点了的
create proc betweenday
@bday as varchar(50),@eday as varchar(50)
as
begin
select * from talbe where (year(getdate())+ '-' + month(生日字段) + '-' + day(生日字段)) between year(getdate())+ '-' + @bday and year(getdate())+ '-' + @eday
end
go
sql中调用
exec betweenday '1-10','1-13'
说明:
CURDATE():SQL命令,取得当前日期
{Fn Year()}:SQL命令,取得时间格式的年部分,{Fn Month()}、Day()类似;
DateADD:SQL命令,日期加。
......
Dim sql As String
Dim iDate, aftDate
iDate = Format(Text1.Text, "MM-DD-YYYY")
aftDate = Format(DateAdd("d", 2, iDate), "MM-DD-YYYY")
'Debug.Print aftDate
sql = "select * from tb_birthday " & _
"where birthday between #" & iDate & "# and #" & aftDate & "#"
......
在text1中输入:2004-12-30 查询结果:
2004-12-30
2004-12-31
2005-1-1 在text1中输入:2004-12-31 查询结果:
2004-12-31
2005-1-1
2005-1-2
然后在sql语句里面用between子句
楼主试试看啦!
当天2004-12-31日,生日NN年1月1日
你的就成了
2004年1月1日 between 2004-12-31日 and 2005-1-2日
能行吗。
劝楼主多分几段,按实际情况写几个sql就行了。
SELECT * FROM TABLENAME WHERE ({fn Year(CURDATE())}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE())) or (({fn Year(CURDATE())+1)}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))
SELECT * FROM TABLENAME WHERE (({fn Year(CURDATE())}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))*(Datediff(Day,CURDate(),{fn Year(CURDATE())}+'-12-31')>=2)+(({fn Year(CURDATE())+1)}+'-'+{fn Month(日期字段)}+'-'+Day(日期字段 ) BETWEEN {fn Year(CURDATE())}+'-'+{fn Month(CURDATE() )}+'-'+Day(CURDATE()) AND DateADD(Day,2,CURDATE()))*(Datediff(Day,CURDate(),{fn Year(CURDATE())}+'-12-31')<2) )
试试:
Dim sql As String
Dim iDate(2) As String
Dim iNow, i
iNow = Format(Text1.Text, "MM-DD-YYYY")
For i = 0 To 2
iDate(i) = Format(DateAdd("d", i, iNow), "M-D")
Next i
sql = "select * from tb_birthday " & _
"where (month(birthday) & '-'& day(birthday)) "& _
"in ('" & iDate(0) & "', '" & iDate(1) & "','" & iDate(2) & "')"
FROM TABLENAME
WHERE (STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0, { fn MONTH(日期字段) }), 1, 0,
'2004-') BETWEEN STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0,
{ fn MONTH(GETDATE()) }), 1, 0, '2004-') AND DATEADD(Day, 2,
STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0, { fn MONTH(GETDATE()) }), 1, 0,
'2004-'))) OR
(DATEADD(Year, 1, STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0,
{ fn MONTH(日期字段) }), 1, 0, '2004-')) BETWEEN
STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0, { fn MONTH(GETDATE()) }), 1, 0,
'2004-') AND DATEADD(Day, 2, STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0,
{ fn MONTH(GETDATE()) }), 1, 0, '2004-')))
FROM TABLENAME
WHERE (DATEDIFF(Day, STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0,
{ fn MONTH(日期字段) }), 1, 0, '2004-'), STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'),
1, 0, { fn MONTH(GETDATE()) }), 1, 0, '2004-')) <= 0) AND (DATEDIFF(day,
STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0, { fn MONTH(日期字段) }), 1, 0,
'2004-'), DATEADD(Day, 2, STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0,
{ fn MONTH(GETDATE()) }), 1, 0, '2004-'))) >= 0) OR
(DATEDIFF(Day, DATEADD(Year, 1, STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0,
{ fn MONTH(日期字段) }), 1, 0, '2004-')), STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0,
'-'), 1, 0, { fn MONTH(GETDATE()) }), 1, 0, '2004-')) <= 0) AND (DATEDIFF(Day,
DATEADD(Year, 1, STUFF(STUFF(STUFF(DAY(日期字段), 1, 0, '-'), 1, 0,
{ fn MONTH(日期字段) }), 1, 0, '2004-')), DATEADD(Day, 2,
STUFF(STUFF(STUFF(DAY(GETDATE()), 1, 0, '-'), 1, 0, { fn MONTH(GETDATE()) }), 1, 0,
'2004-'))) >= 0)
这个对了,偶调试过了。真晕!!!!!!
看了这么多人回复还是 samwzhang() 比较执着,不过其可读性也。过两天结贴吧,再顺便贴下我的,看还有不有高人到场!
create function GetBirth(@birthdate datetime)
returns @birthTable table (client_name varchar(30),birth datetime)
as
begin
if month(@birthdate)=month(@birthdate+2) then
insert into @birthtable select client_name,birth from clienttable
where month(birth)=month(@birthdate)
and day(birth)>=day(@birthdate)and day(birth)<=day(@birthdate+2)
else
insert into @birthtable select client_name,birth from clienttable
where (month(birth)=month(@birthdate) and and day(birth)>=day(@birthdate))
or ((month(birth)=month(@birthdate+2) and day(birth)<=day@birthdate+2))
return
end--调用:
select * from getbirth('2003-02-28')
可读性:SQL语句<存储过程<程序
你出题的时候只要求最少的SQL语句,那偶自然是一句话给你实现了。
说话不算,尽耍赖。
select client_name,birth from clienttable
where month(birth)=month(getdate())
and day(birth)>=day(getdate())and day(birth)<=day(getdate()+2)
else
select client_name,birth from clienttable
where (month(birth)=month(getdate()) and and day(birth)>=day(getdate()))
or ((month(birth)=month(getdate()+2) and day(birth)<=day(getdate()+2))这样也不能提交的吗?