select case combo1.text
case "昨天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })-1 "
case "今天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })"
case "明天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() }) and day(date)=DAY({ fn NOW() })+1"
case "上月"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) -1 and MONTH(date) = MONTH({ fn NOW() })-1'"
case "本月"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })"
case "下月"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() })+1 and MONTH(date) = MONTH({ fn NOW() })+1"
真够累的!
星期比较麻烦,等闲时再答吧.
case "昨天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })-1 "
case "今天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })"
case "明天"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() }) and day(date)=DAY({ fn NOW() })+1"
case "上月"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) -1 and MONTH(date) = MONTH({ fn NOW() })-1'"
case "本月"
sql= "select * from table where YEAR(xdate1) > YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })"
case "下月"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() })+1 and MONTH(date) = MONTH({ fn NOW() })+1"
真够累的!
星期比较麻烦,等闲时再答吧.
再解释一下
======================
比如字段名为 LASTLOGIN那么,“今天”的条件就是
LASTLOGIN在今天时间范围内的内容
“明天”就是LASTLOGIN在明天时间范围内的内容
其它雷同明白?
=============================
select case combo1.text
case "昨天"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })-1 "
case "今天"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })and day(date)=DAY({ fn NOW() })"
case "明天"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() }) and day(date)=DAY({ fn NOW() })+1"
case "上月"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() }) -1 and MONTH(date) = MONTH({ fn NOW() })-1'"
case "本月"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() }) and MONTH(date) = MONTH({ fn NOW() })"
case "下月"
sql= "select * from table where YEAR(xdate1) = YEAR({ fn NOW() })+1 and MONTH(date) = MONTH({ fn NOW() })+1"
select d-1 from testtable今天:
select d from testtable明天:
select d+1 from testtable上周:
Set rs = cn.Execute("select d-datepart('w',d)-6,d-datepart('w',d) from testtable")
或
select dateadd('ww',-1,d-datepart('w',d)+1,),dateadd('ww',-1,d-datepart('w',d)+7) from testtable本周:
select d-datepart('w',d)+1,d-datepart('w',d)+7 from testtable下周:
select d-datepart('w',d)+8,d-datepart('w',d)+14 from testtable
或
select dateadd('ww',1,d-datepart('w',d)+1,),dateadd('ww',1,d-datepart('w',d)+7) from testtable上月:
select dateadd('m',-1,d-datepart('d',d)+1),d-datepart('d',d) from testtable本月:
select d-datepart('d',d)+1,dateadd('m',1,d-datepart('d',d)) from testtable下月:
select dateadd('m',1,d-datepart('d',d)+1),dateadd('m',2,d-datepart('d',d)) from testtable
Dim b As Date
a = Now + (32 - CLng(Format(Now, "dd")))
b='LASTLOGIN'
if format(a,"yyyy/mm")=format(b,"yyyy/mm") then
msgbox "下个月"
end if
select case combo1.text
case "昨天"
sql= "select * from table where YEAR(xdate1) = " & Year(dateadd("d",-1,now())) & " and MONTH(date) = " & month(dateadd("d",-1,now())) & " and day(date)=" & Day(dateadd("d",-1,now()))
.......
太多了,就改一个吧,就是这个意思!!!
你的方法行吗?...:)
我的程序经过测试的,可询语句中的d是日期字段,你试试吧,花了我不少时间,该加分了吧!
select * form table where d between (d-datepart('w',d)-6) and (d-datepart('w',d))
select * form table where cdate(format(d,'yyyy-mm-dd')) between (d-datepart('w',d)-6) and (d-datepart('w',d))
================================================================函数now()返回的就是今天的日期,你是二星用户,不用我说得那么详细吧!select * form table where cdate(format(你的日期字段名,'yyyy-mm-dd')) between (now()-datepart('w',now())-6) and (now()-datepart('w',now()))
dteTemp = DateAdd("d", -1, Date)
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("d", 1, dteTemp) & "#"
Case "今天"
dteTemp = Date
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("d", 1, dteTemp) & "#"
Case "上周"
dteTemp = DateAdd("ww", -1, Date)
dteTemp = DateAdd("d", 1 - Weekday(dteTemp, vbMonday), dteTemp)
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("ww", 1, dteTemp) & "#"
Case "本周"
dteTemp = Date
dteTemp = DateAdd("d", 1 - Weekday(dteTemp, vbMonday), dteTemp)
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("ww", 1, dteTemp) & "#"
Case "上一月"
dteTemp = DateAdd("m", -1, Date)
dteTemp = DateAdd("d", 1 - Day(dteTemp), dteTemp)
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("m", 1, dteTemp) & "#"
Case "本月"
dteTemp = Date
dteTemp = DateAdd("d", 1 - Day(dteTemp), dteTemp)
strChild = strField & ">= #" & dteTemp & "# AND " & _
strField & " < #" & DateAdd("m", 1, dteTemp) & "#"这是我自己写的,为什么不用Between?因为between还需要在最大值中加上
23:59:59,所以,就用了>= and <
DateValue函数来做的比如,今天
datevalue(fld1)=#2002-2-2#上周:
datevalue(fld1) between ... and ...
使用SQL函数和不使用SQL函数的速度哪个快呢?
(我认为该是不使用的快)Between and 和 >= and <=比较哪个速度快呢?
不使用SQL函数的速度快,特别是在这个字段有索引的情况。
>>Between and 和 >= and <=比较哪个速度快呢?
这两个速度是一样的。