问题:给定两个日期 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 请大家帮忙!
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 请大家帮忙!
TO GGL123():用存储过程也可.
写一个存储过程:
CREATE PROCEDURE getxxx
@date1 smalldatetime, --起始年月日
@date2 smalldatetime --结束年月日AScreate 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=0declare @tempdate smalldatetime --临时变量
set @tempdate=@date1while @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
endselect * 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 = NothingEnd 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只是个示例,自己完善
在窗体上画下datagrid
下面找码考进去运行一下 OKiamtsfw(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.OpenDim 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 = RsEnd 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