查询时间段的sql如何写？

[DawnWoo]

字段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语句？先谢谢了

[DawnWoo]

access是不是没有convert函数啊

[victorycyz]

Access:

select *
from tablename
where ( year(d_date) between 1999 and 2002 )
and ( format(d_date,"yymm") between "0202" and "0515" )

[xinliangyu]

在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")

[DawnWoo]

SELECT *
FROM tablename
WHERE (YEAR(D_DATE) BETWEEN 1999 AND 2002)
AND (Right(Format(D_DATE,'YYMMDD'),4) BETWEEN '0202' AND '0515')

[DawnWoo]

结帖了，又出现问题了，跨年度问题如何解决？

[DawnWoo]

结帖了，又出现问题了，跨年度问题如何解决？
比如查询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语句？谢谢

[victorycyz]

举一反三啦。

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]

酱紫是不对的
**********************************
如下：过滤不掉首尾的纪录：就是
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]

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]

最终答案是：
================================================================================
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'))
================================================================================

[HenryXiaoY]

Select d1,d2 from tableName where D_Date between '1999/2/2' and '2002/5/15'
试试

[tztz520]

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

[victorycyz]

select *
from tablename
where ( year(d_date) between 1999 and 2002 )
and ( right(convert(varchar(8),d_date,112),4) between '0202' and '0515' )