求指定两个日期之间的所有季度的首日和尾日集合?
问题:给定两个日期 date1,date2,需要求所两个日期之间的每一个季度的开始日期和结束日期,及季度名称,年份的集合,第一个季度的起始日为date1,最后一个季度的结束日期 date2,如:
date1=2001-5-3
date2=2002-3-1
集合应该是:
年 季度 起始日 结束日
2001 2 2001-5-3 2001-6-30
2001 3 2001-7-1 2001-9-30
2001 4 2001-10-1 2001-12-30
2002 1 2002-1-5 2002-3-1
请大家帮忙!
[解决办法]
我这么解决的：
写一个存储过程：
CREATE PROCEDURE getxxx
@date1 smalldatetime, --起始年月日
@date2 smalldatetime --结束年月日
AS
create table #xxx
(a varchar(100),
b varchar(100),
c varchar(100),
d varchar(100),
)
declare @qcount integer --存储起始年月日与结束年月日之间的季度数
set @qcount=datediff(q,@date1,@date2)
declare @i integer --循环变量控制季度数
set @i=0
declare @tempdate smalldatetime --临时变量
set @tempdate=@date1
while @i <@qcount+1 --开始循环
begin
declare @temp smalldatetime --某个季度的最后一天
set @temp=dateadd(month,3-(datepart(month,@tempdate)-1)%3,@tempdate-day(@tempdate)+1)-1
if @i <> @qcount
begin
insert into #xxx select datepart(yyyy,@tempdate) , datepart(q,@tempdate),@tempdate,@temp
end
else --如果循环到最后一个季度，使用最后日期
begin
insert into #xxx select datepart(yyyy,@tempdate) , datepart(q,@tempdate),@tempdate,@date2
end
set @tempdate=dateadd(d,1,@temp)
set @i=@i+1
end
select * from #xxx
drop table #xxx
GO
再vb中如下调用：
Private Sub Command1_Click()
Dim db As New ADODB.Connection
Dim rec As New ADODB.Recordset
db.Open "driver={SQL Server};server=8FCFAFD4360B4C2;uid=sa;pwd=;database=grain_Buy "
rec.Open "getxxx '2001-5-3 ', '2002-3-1 ' ", db, adOpenDynamic, adLockOptimistic
Do While Not rec.EOF
Debug.Print rec.Fields(0) & " | " & rec.Fields(1) & " | " & rec.Fields(2) & " | " & rec.Fields(3)
rec.MoveNext
Loop
rec.Close
db.Close
Set rec = Nothing
Set db = Nothing
End Sub
结果如下：
2001 | 2 | 05 3 2001 12:00AM | 06 30 2001 12:00AM
2001 | 3 | 07 1 2001 12:00AM | 09 30 2001 12:00AM
2001 | 4 | 10 1 2001 12:00AM | 12 31 2001 12:00AM
2002 | 1 | 01 1 2002 12:00AM | 03 1 2002 12:00AM
不知是否满足要求
[解决办法]
类中：
Public Year As String
Public Season As Integer
Public Starting_Time As String
Public Ending_Time As String
窗体中：
Function test(s As String, e As String) As Collection
Dim c As New Collection
Dim i As Integer
Dim temp As String
'获得二个日期之间有几个季
Dim q As Integer
q = DateDiff( "q ", CDate(s), CDate(e))
'循环获取每个季节的相关数据，并加入集合
For i = 0 To q
Dim cls As New Class1
If i = 0 Then
temp = s
End If
'当前年
cls.Year = Format(temp, "yyyy ")
'当前是第几季
cls.Season = DatePart( "q ", CDate(temp))
'起始日期
cls.Starting_Time = Format(temp, "yyyy-mm-dd ")
'终止日期
Select Case cls.Season
Case 1
cls.Ending_Time = cls.Year & "-03-31 "
Case 2
cls.Ending_Time = cls.Year & "-06-30 "
Case 3
cls.Ending_Time = cls.Year & "-09-30 "
Case 4
cls.Ending_Time = cls.Year & "-12-31 "
End Select
If i = q Then cls.Ending_Time = e
cls.Ending_Time = Format(cls.Ending_Time, "yyyy-mm-dd ")
'添加到集合
c.Add cls
temp = DateAdd( "d ", 1, cls.Ending_Time)
Set cls = Nothing
Next
Set test = c
Set c = Nothing
End Function
Private Sub Command1_Click()
Dim col As New Collection
Dim cls As Class1
Dim i As Integer
Set col = test( "2001-5-3 ", "2002-3-1 ")
For i = 1 To col.Count
Set cls = col.Item(i)
Debug.Print cls.Year; cls.Season; cls.Starting_Time; cls.Ending_Time
Set cls = Nothing
Next
End Sub
只是个示例，自己完善
[解决办法]
纯VB作品:
在窗体上画下datagrid
下面找码考进去运行一下 OK
iamtsfw(iamtsfw) 用存储过程解决的也行
Private Sub Form_Load()
Dim Date1 As Date
Dim Date2 As Date
Date1 = #5/3/2001#
Date2 = #3/1/2002#
Dim intAa As Integer
intAa = DateDiff( "Q ", Date1, Date2)
Dim Rs As New ADODB.Recordset
Rs.Fields.Append "年份 ", adBSTR
Rs.Fields.Append "季度 ", adBSTR
Rs.Fields.Append "起始日期 ", adBSTR
Rs.Fields.Append "终止日期 ", adBSTR
Rs.Open
Dim Jd As Integer
Dim I As Long
Dim strQsrq As Date
Dim strZzrq As Date
Dim aNf As Long
aNf = Val(Mid(Format$(Date1, "yyyy-mm-dd "), 1, 4))
Jd = DatePart( "q ", Date1)
For I = 0 To intAa
If I = 0 Then
Rs.AddNew
Rs!年份 = aNf
Rs!季度 = Jd
mkRq aNf, Jd, strQsrq, strZzrq
Rs!起始日期 = Date1
Rs!终止日期 = strZzrq
Else
Rs.AddNew
Rs!年份 = aNf
Rs!季度 = Jd
mkRq aNf, Jd, strQsrq, strZzrq
Rs!起始日期 = strQsrq
Rs!终止日期 = strZzrq
End If
If Jd = 4 Then aNf = aNf + 1: Jd = 1 Else Jd = Jd + 1
If I = intAa Then Rs!终止日期 = Date2
Next
Set DataGrid1.DataSource = Rs
End Sub
'跟据给定年份和季度返回其起始日期
Function mkRq(Year1 As Long, Jd As Integer, Qsrq As Date, zzrq As Date)
Select Case Jd
Case 1
Qsrq = Year1 & "-1-1 "
zzrq = Year1 & "-3-31 "
Case 2
Qsrq = Year1 & "-4-1 "
zzrq = Year1 & "-6-30 "
Case 3
Qsrq = Year1 & "-7-1 "
zzrq = Year1 & "-9-30 "
Case 4
Qsrq = Year1 & "-10-1 "
zzrq = Year1 & "-12-31 "
End Select
End Function
