1181
社区成员
- 主页
查询时间段的sql如何写？
字段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语句？先谢谢了
我的查询要求：比如查询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语句？先谢谢了
...全文
请发表友善的回复…
发表回复
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' )
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
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'))
================================================================================
================================================================================
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" )
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)
==================================
咋整？
**********************************
如下：过滤不掉首尾的纪录：就是
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语句？谢谢
比如查询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')
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")
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" )
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函数啊