查询时间段的sql如何写?

DawnWoo 2004-10-13 11:52:03
字段D_DATE,值的类型是DATE型,具体数据如:1999-1-1
我的查询要求:比如查询1999-2-2到2002-5-15之间的数据,其中年是1999-2002,月日则是每年的2-2到5-15之间的数据,就像这样
D_DATE , d1, d2
1999-2-2 xx xx
... ... ...
1999-5-15 xx xx
2000-2-2 xx xx
... ... ...
2000-5-15 xx xx
2001-2-2 xx xx
... ... ...
2001-5-15 xx xx
2002-2-2 xx xx
... ... ...
2002-5-15 xx xx
怎么写sql语句?先谢谢了
...全文
106 点赞 收藏 13
写回复
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
victorycyz 2004-10-13
select *
from tablename
where ( year(d_date) between 1999 and 2002 )
and ( right(convert(varchar(8),d_date,112),4) between '0202' and '0515' )
回复
tztz520 2004-10-13
Private Sub Command15_Click()
Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim a As Date, b As Date
a = #2/2/1999#
b = #5/15/2002#
db.ConnectionString = "provider=microsoft.jet.oledb.4.0;" & "data source=" & App.Path & "\dev.mdb"
db.Open
rs.Open "select * from tablename where DATE between #" & a & "# and #" & b & "#", db
End Sub
回复
HenryXiaoY 2004-10-13
Select d1,d2 from tableName where D_Date between '1999/2/2' and '2002/5/15'
试试
回复
DawnWoo 2004-10-13
最终答案是:
================================================================================
SELECT *
FROM
(SELECT D_DATE d1,d2
FROM tablename
WHERE (D_DATE BETWEEN #1999-12-2# AND #2002-3-15#))
WHERE ((Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '1202' AND '1231'))
OR ((Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '0101' AND '0305'))
================================================================================
回复
victorycyz 2004-10-13
select *
from tablename
where ( d_date between '1999-12-2' and '2002-3-15' )
and ( format(d_date,"yymm") > "1202" or format(d_date,"yymm") < "0315" )
回复
DawnWoo 2004-10-13
酱紫是不对的
**********************************
如下:过滤不掉首尾的纪录:就是
1999的出现了1-1到3-15的纪录,这是不要的
2002的出现了12-2到12-31的纪录,这也是不要的
==================================
SELECT * FROM
(SELECT D_DATE,d1,d2
FROM tablename
WHERE ((Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '1202' AND '1231')
OR (Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '0101' AND '0315')))
WHERE (YEAR(D_DATE) BETWEEN 1999 AND 2002)
==================================
咋整?

回复
victorycyz 2004-10-13

举一反三啦。

Access:

select *
from tablename
where ( year(d_date) between 1999 and 2002 )
and ( format(d_date,"yymm") > "1202" or format(d_date,"yymm") < "0315" )
回复
DawnWoo 2004-10-13
结帖了,又出现问题了,跨年度问题如何解决?
比如查询1999-12-2到2002-3-15之间的数据,其中年是1999-2002,月日则是每年的12-2到3-15之间的数据,就像这样
D_DATE , d1, d2
1999-12-2 xx xx
... ... ...
1999-12-31 xx xx
2000-1-1 xx xx
... ... ...
2000-3-15 xx xx
2000-12-2 xx xx
2000-1-1
... ... ...
2000-3-15 xx xx
2001-12-2 xx xx
2001-12-31
2002-1-1
... ... ...
2002-3-15 xx xx
怎么写sql语句?谢谢
回复
DawnWoo 2004-10-13
结帖了,又出现问题了,跨年度问题如何解决?
回复
DawnWoo 2004-10-13
SELECT *
FROM tablename
WHERE (YEAR(D_DATE) BETWEEN 1999 AND 2002)
AND (Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '0202' AND '0515')
回复
xinliangyu 2004-10-13
在access的SQL中,year,month ,day三函数可分别从有效日期参数中取得年,月,日,所以可大致如次:
select * from 表名 where
(year(日期字段名) between 1999 and 2002)
and
(mid(format(日期字段名,"yyyy-m-dd"),6)>="2-2")
and
(mid(format(日期字段名,"yyyy-m-dd"),6)<="5-15")
回复
victorycyz 2004-10-13
Access:

select *
from tablename
where ( year(d_date) between 1999 and 2002 )
and ( format(d_date,"yymm") between "0202" and "0515" )
回复
DawnWoo 2004-10-13
access是不是没有convert函数啊
回复
发动态
发帖子

1181

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
社区公告
暂无公告