1.建议用datetime类型的字段,存储到一个字段中,这样可以直接比较2.分开的话,要自己接sql select * from 表 where cast((年字段+月字段+日字段) as datetime) between '2001-01-01' and '2004-01-01'
若果你数据库中这三个字段都是int型的可以这样: Select * From [Table1] Where (mYear Between 1992 And 2004) And (mMonth Between 2 And 9) And (mDay Between 1 And 9)
ryuginka(除了我谁敢用真名:刘银华)2.分开的话,要自己接sql select * from 表 where cast((年字段+月字段+日字段) as datetime) between '2001-01-01' and '2004-01-01' 请问一下,你的那个转换那块就直接把相应的字段名填进去就可以了吗?我刚才试一一下不行,我的sql语句是 select needflow from tab_industrywaterneed where industryconsumer='成都市区' and cast((years+months+days) as datetime) between '2001-2-13' and '2004-3-1'为什么不对呢,请继续讲解一下 谢谢以上各位好心人。
cSql ="select * from TABLE WHERE Format(years+'-'+months+'-'+days,'YYYY-MM-DD') between '2001-01-01' and '2004-01-01'"
在SQLServer里可以运行行,oracle不熟悉,你查查有无对应的函数吧。
我这里用了一个笨方法不过已经解决这个问题了 Sqlstr = "select * from (select * from (select * from table where years between " & startyear & " and " & endyear & “) where months between " & startmonth & " and " & endmonth & ") where days between " & startday & " and " & endday
楼主这样应该不行的吧?你这样查询会遗漏记录的,你可以测试一下看看。例如你要查询 1998-3-1 至 2004-1-1之间的数据,而表中有一条这样的记录 years : 1998 months : 9 days : 3 用你上面的查询就查不出来了
Oracle中应该有像SQLServer“Str”这样的数字转字符函数吧,你找找看。
这是在SQLServer中不使用“Str”函数的方法,已通过查询分析器的调试。是查询1998-3-1 至 2004-1-1之间的数据,其中对应关系: @aY:startyear @bY:endyear @aM:startmonth @bM:endmonth @aD:startday @bD: endday========================================================Declare @aY As Int Declare @bY As Int Declare @aM As Int Declare @bM As Int Declare @aD As Int Declare @bD As IntSet @aY = 1998 Set @bY = 2004 Set @aM = 3 Set @bM = 1 Set @aD = 1 Set @bD = 1Select * From (Select * From (Select * From [Table2] Where (mYear Between @aY And @bY)) As a Where a.mMonth >= Case a.mYear When @aY Then @aM Else 1 End And a.mMonth <= Case a.mYear When @bY Then @bM Else 12 End ) As b Where b.mDay >= Case b.mYear When @aY Then Case b.mMonth When @aM Then @aD Else 1 End When @bY Then Case b.mMonth When @bM Then 1 Else 1 End Else 1 End And b.mDay <= Case b.mYear When @aY Then Case b.mMonth When @bM Then 31 Else 31 End When @bY Then Case b.mMonth When @bM Then @bD Else 31 End Else 31 End
select * from 表 where cast((年字段+月字段+日字段) as datetime) between '2001-01-01' and '2004-01-01'
Select * From [Table1] Where (mYear Between 1992 And 2004) And (mMonth Between 2 And 9) And (mDay Between 1 And 9)
select * from 表 where cast((年字段+月字段+日字段) as datetime) between '2001-01-01' and '2004-01-01'
请问一下,你的那个转换那块就直接把相应的字段名填进去就可以了吗?我刚才试一一下不行,我的sql语句是 select needflow from tab_industrywaterneed where industryconsumer='成都市区' and cast((years+months+days) as datetime) between '2001-2-13' and '2004-3-1'为什么不对呢,请继续讲解一下
谢谢以上各位好心人。
若果你数据库中这三个字段都是int型的可以这样:
Select * From [Table1] Where (mYear Between 1992 And 2004) And (mMonth Between 2 And 9) And (mDay Between 1 And 9)
你这个有个问题,因为查询是根据用户来定的,有可能用户选择的时间是‘2001-7-29',而结束时间有可能是'2004-1-12'如果这样的话,你的那个算法就不行的。根本选不出任何结果来
那几个字段在oracle数据库中number类型的数据
between '2001-01-01' and '2004-01-01'"
Sqlstr = "select * from (select * from (select * from table where years between " & startyear & " and " & endyear & “) where months between " & startmonth & " and " & endmonth & ") where days between " & startday & " and " & endday
years : 1998 months : 9 days : 3
用你上面的查询就查不出来了
@aY:startyear
@bY:endyear
@aM:startmonth
@bM:endmonth
@aD:startday
@bD: endday========================================================Declare @aY As Int
Declare @bY As Int
Declare @aM As Int
Declare @bM As Int
Declare @aD As Int
Declare @bD As IntSet @aY = 1998
Set @bY = 2004
Set @aM = 3
Set @bM = 1
Set @aD = 1
Set @bD = 1Select * From
(Select * From
(Select * From [Table2] Where (mYear Between @aY And @bY)) As a
Where a.mMonth >=
Case a.mYear
When @aY Then @aM
Else 1
End
And a.mMonth <=
Case a.mYear
When @bY Then @bM
Else 12
End ) As b Where b.mDay >=
Case b.mYear
When @aY Then
Case b.mMonth
When @aM Then @aD
Else 1
End
When @bY Then
Case b.mMonth
When @bM Then 1
Else 1
End
Else 1
End
And b.mDay <=
Case b.mYear
When @aY Then
Case b.mMonth
When @bM Then 31
Else 31
End
When @bY Then
Case b.mMonth
When @bM Then @bD
Else 31
End
Else 31
End