主题：[求助]日期时间比较

 老师，我有列是DATETIME类型的数据列，想分别取日期和时间出来做比较和计算，该怎么做。
比如“2017-12-05 05：49”我要统计等于这一天（日期是变量，不是只统计这一天的），且时间小于06:05的结果：
Dim d As Date = Date.Today
Dim dn As Integer = d.DayOfWeek
Dim sd As Date
If dn=6 Then
    sd=d
Else
    sd=d.AddDays(-dn-1)
End If
dr("周六_早上_出勤") = Tables("欢迎").Compute("Count(VipLog_DateTime)","SN = '" & Vals(i)(0) & "'and convert(Date,VipLog_DateTime) = " & sd & "' and convert(Time,VipLog_DateTime) between #00:00# and #06:05# ")    

Dim d As Date = Date.Today
Dim dn As Integer = d.DayOfWeek
Dim sd As Date
If dn=6 Then
    sd=d
Else
    sd=d.AddDays(-dn-1)
End If
Dim sd2 As Date = sd.AddHours(6).AddMinutes(5)
dr("周六_早上_出勤") = Tables("欢迎").Compute("Count(VipLog_DateTime)","SN = '" & Vals(i)(0) & "'and VipLog_DateTime >= #" & sd & "# and VipLog_DateTime <= #" & sd2 & "#")    

老师因为我要分成好多列出报表（如下）显示一周的出勤情况（周六-周五），有没有什么其他好方法，不然需要定义很多的变量。
“”周六_早上_出勤","周六_早上_迟到","周六_早上_旷工","周六_早上_早退","周六_下午_出勤","周六_下午_迟到","周六_下午_旷工","周六_下午_早退","周六_晚上加班",
"周日_早上_出勤","周日_早上_迟到","周日_早上_旷工","周日_早上_早退","周日_下午_出勤","周日_下午_迟到","周日_下午_旷工","周日_下午_早退","周日_晚上加班",
"周一_早上_出勤","周一_早上_迟到","周一_早上_旷工","周一_早上_早退","周一_下午_出勤","周一_下午_迟到","周一_下午_旷工","周一_下午_早退","周一_晚上加班",
"周二_早上_出勤","周二_早上_迟到","周二_早上_旷工","周二_早上_早退","周二_下午_出勤","周二_下午_迟到","周二_下午_旷工","周二_下午_早退","周二_晚上加班",
"周三_早上_出勤","周三_早上_迟到","周三_早上_旷工","周三_早上_早退","周三_下午_出勤","周三_下午_迟到","周三_下午_旷工","周三_下午_早退","周三_晚上加班",
"周四_早上_出勤","周四_早上_迟到","周四_早上_旷工","周四_早上_早退","周四_下午_出勤","周四_下午_迟到","周四_下午_旷工","周四_下午_早退","周四_晚上加班",
"周五_早上_出勤","周五_早上_迟到","周五_早上_旷工","周五_早上_早退","周五_下午_出勤","周五_下午_迟到","周五_下午_旷工","周五_下午_早退","周五_晚上加班"}

上例子

1、你的列名定义规律一些，比如定义成 日1_1_1， 日1_1_2， 日1_2_1，表示：星期1_早上1_出勤1

 

     这样命名，可以减少编码量。

 

2、具体问题上传实例说明，说明具体逻辑。

老师，请查阅附件，我自己写了段代码在窗口按钮事件，但是计算速度相当慢，大概要两分钟。

下载信息  [文件大小：   下载次数： ]

点击浏览该文件:管理项目6.rar

汗

 

Dim d As Date = Date.Today
Dim dn As Integer = d.DayOfWeek
Dim sd As Date
If dn=6 Then
    sd=d
Else
    sd=d.AddDays(-dn-1)
End If
Dim v1() As String = {"周六_早上_出勤","周六_早上_迟到","周六_早上_旷工","周六_早上_早退","周六_下午_出勤","周六_下午_迟到","周六_下午_旷工","周六_下午_早退","周六_晚上加班","周日_早上_出勤","周日_早上_迟到","周日_早上_旷工","周日_早上_早退","周日_下午_出勤","周日_下午_迟到","周日_下午_旷工","周日_下午_早退","周日_晚上加班","周一_早上_出勤","周一_早上_迟到","周一_早上_旷工","周一_早上_早退","周一_下午_出勤","周一_下午_迟到","周一_下午_旷工","周一_下午_早退","周一_晚上加班","周二_早上_出勤","周二_早上_迟到","周二_早上_旷工","周二_早上_早退","周二_下午_出勤","周二_下午_迟到","周二_下午_旷工","周二_下午_早退","周二_晚上加班","周三_早上_出勤","周三_早上_迟到","周三_早上_旷工","周三_早上_早退","周三_下午_出勤","周三_下午_迟到","周三_下午_旷工","周三_下午_早退","周三_晚上加班","周四_早上_出勤","周四_早上_迟到","周四_早上_旷工","周四_早上_早退","周四_下午_出勤","周四_下午_迟到","周四_下午_旷工","周四_下午_早退","周四_晚上加班","周五_早上_出勤","周五_早上_迟到","周五_早上_旷工","周五_早上_早退","周五_下午_出勤","周五_下午_迟到","周五_下午_旷工","周五_下午_早退","周五_晚上加班"}
Dim v2(v1.length-1) As Integer
For j As Integer = 0 To v2.length-1
    v2(j) = 0
Next
Dim dtb As New DataTableBuilder("统计测试")
dtb.adddef("卡号",Gettype(Integer),6)
dtb.AddDef("姓名", Gettype(String), 100)
dtb.AddDef("公司", Gettype(String), 64)
dtb.AddDef("岗位", Gettype(String), 64)
dtb.AddDef("应出勤", Gettype(Integer), 6)
For Each prd As String In v1
    dtb.AddDef(prd, Gettype(Double))
Next
dtb.Build()
Dim pr As DataRow = Nothing
DataTables("统计测试").StopRedraw
Dim drs = DataTables("欢迎").Select("", "SN,Chinese_Name,Company,VipLog_DateTime")
For i As Integer = 0 To drs.count
    Dim dr As DataRow = Nothing
    If i < drs.count Then
        dr = drs(i)
    End If
    If i = drs.count OrElse pr IsNot Nothing AndAlso (dr("sn") <> pr("sn") OrElse dr("Chinese_Name") <> pr("Chinese_Name") OrElse dr("Company") <> pr("Company")) Then
        Dim ndr As DataRow = DataTables("统计测试").AddNew()
        ndr("卡号") = pr("SN")
        ndr("姓名") = pr("Chinese_Name")
        ndr("公司") = pr("Company")
        ndr("岗位") = pr("Position")
        ndr("应出勤") = 1
        For n As Integer = 0 To dn+1
            ndr(v1(n*9)) = v2(n*9)
            ndr(v1(n*9+1)) = v2(n*9+1)
            ndr(v1(n*9+2)) = ndr("应出勤")-ndr(v1(n*9)) - ndr(v1(n*9+1))
            ndr(v1(n*9+3)) = v2(n*9+3)
            ndr(v1(n*9+4)) = v2(n*9+4)
            ndr(v1(n*9+5)) = v2(n*9+5)
            ndr(v1(n*9+6)) = ndr("应出勤")-ndr(v1(n*9+4)) - ndr(v1(n*9+5))
            ndr(v1(n*9+7)) = v2(n*9+7)
            ndr(v1(n*9+8)) = v2(n*9+8)
        Next
        For j As Integer = 0 To v2.length-1
            v2(j) = 0
        Next
        If i = drs.count Then
            Exit For
        End If
    End If
    Dim dtime As Date = dr("VipLog_DateTime")
    For n As Integer = 0 To dn+1
        Dim t1 As Date = sd.AddDays(n)
        Dim t2 As Date = sd.AddDays(n).AddHours(6).AddMinutes(5)
        Dim t3 As Date = sd.AddDays(n).AddHours(11)
        Dim t4 As Date = sd.AddDays(n).AddHours(11).AddMinutes(30)
        Dim t5 As Date = sd.AddDays(n).AddHours(12).AddMinutes(10)
        Dim t6 As Date = sd.AddDays(n).AddHours(13).AddMinutes(05)
        Dim t7 As Date = sd.AddDays(n).AddHours(17)
        Dim t8 As Date = sd.AddDays(n).AddHours(17).AddMinutes(30)
        Dim t9 As Date = sd.AddDays(n).AddHours(20)
        Dim t10 As Date = sd.AddDays(n).AddHours(23).AddMinutes(59)
        If dtime >= t1 AndAlso dtime <= t2 Then
            v2(n*9) += 1
            Exit For
        ElseIf dtime > t2 AndAlso dtime <= t3 Then
            v2(n*9+1) += 1
            Exit For
        ElseIf dtime > t3 AndAlso dtime <= t4 Then
            v2(n*9+3) += 1
            Exit For
        ElseIf dtime > t5 AndAlso dtime <= t6 Then
            v2(n*9+4) += 1
            Exit For
        ElseIf dtime > t6 AndAlso dtime <= t7 Then
            v2(n*9+5) += 1
            Exit For
        ElseIf dtime > t7 AndAlso dtime <= t8 Then
            v2(n*9+7) += 1
            Exit For
        ElseIf dtime > t9 AndAlso dtime <= t10 Then
            v2(n*9+8) += 1
            Exit For
        End If
    Next
    pr = dr
Next
MainTable= Tables("统计测试")
Dim t As Table = Tables("统计测试")
Dim g As Subtotalgroup
t.SubtotalGroups.Clear()
t.GroupAboveData = True
t.TreeVisible = True
t.SpillNode = True

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "岗位"
g.TotalOn = "应出勤,周六_早上_出勤,周六_早上_迟到,周六_早上_旷工,周六_早上_早退,周六_下午_出勤,周六_下午_迟到,周六_下午_旷工,周六_下午_早退,周六_晚上加班,周日_早上_出勤,周日_早上_迟到,周日_早上_旷工,周日_早上_早退,周日_下午_出勤,周日_下午_迟到,周日_下午_旷工,周日_下午_早退,周日_晚上加班,周一_早上_出勤,周一_早上_迟到,周一_早上_旷工,周一_早上_早退,周一_下午_出勤,周一_下午_迟到,周一_下午_旷工,周一_下午_早退,周一_晚上加班,周二_早上_出勤,周二_早上_迟到,周二_早上_旷工,周二_早上_早退,周二_下午_出勤,周二_下午_迟到,周二_下午_旷工,周二_下午_早退,周二_晚上加班,周三_早上_出勤,周三_早上_迟到,周三_早上_旷工,周三_早上_早退,周三_下午_出勤,周三_下午_迟到,周三_下午_旷工,周三_下午_早退,周三_晚上加班,周四_早上_出勤,周四_早上_迟到,周四_早上_旷工,周四_早上_早退,周四_下午_出勤,周四_下午_迟到,周四_下午_旷工,周四_下午_早退,周四_晚上加班,周五_早上_出勤,周五_早上_迟到,周五_早上_旷工,周五_早上_早退,周五_下午_出勤,周五_下午_迟到,周五_下午_旷工,周五_下午_早退,周五_晚上加班"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "公司"
g.TotalOn = "应出勤,周六_早上_出勤,周六_早上_迟到,周六_早上_旷工,周六_早上_早退,周六_下午_出勤,周六_下午_迟到,周六_下午_旷工,周六_下午_早退,周六_晚上加班,周日_早上_出勤,周日_早上_迟到,周日_早上_旷工,周日_早上_早退,周日_下午_出勤,周日_下午_迟到,周日_下午_旷工,周日_下午_早退,周日_晚上加班,周一_早上_出勤,周一_早上_迟到,周一_早上_旷工,周一_早上_早退,周一_下午_出勤,周一_下午_迟到,周一_下午_旷工,周一_下午_早退,周一_晚上加班,周二_早上_出勤,周二_早上_迟到,周二_早上_旷工,周二_早上_早退,周二_下午_出勤,周二_下午_迟到,周二_下午_旷工,周二_下午_早退,周二_晚上加班,周三_早上_出勤,周三_早上_迟到,周三_早上_旷工,周三_早上_早退,周三_下午_出勤,周三_下午_迟到,周三_下午_旷工,周三_下午_早退,周三_晚上加班,周四_早上_出勤,周四_早上_迟到,周四_早上_旷工,周四_早上_早退,周四_下午_出勤,周四_下午_迟到,周四_下午_旷工,周四_下午_早退,周四_晚上加班,周五_早上_出勤,周五_早上_迟到,周五_早上_旷工,周五_早上_早退,周五_下午_出勤,周五_下午_迟到,周五_下午_旷工,周五_下午_早退,周五_晚上加班"
g.Caption = "{0} 小计"
t.SubtotalGroups.Add(g)

g = New Subtotalgroup
g.Aggregate = AggregateEnum.Sum
g.GroupOn = "*"
g.TotalOn = "应出勤,周六_早上_出勤,周六_早上_迟到,周六_早上_旷工,周六_早上_早退,周六_下午_出勤,周六_下午_迟到,周六_下午_旷工,周六_下午_早退,周六_晚上加班,周日_早上_出勤,周日_早上_迟到,周日_早上_旷工,周日_早上_早退,周日_下午_出勤,周日_下午_迟到,周日_下午_旷工,周日_下午_早退,周日_晚上加班,周一_早上_出勤,周一_早上_迟到,周一_早上_旷工,周一_早上_早退,周一_下午_出勤,周一_下午_迟到,周一_下午_旷工,周一_下午_早退,周一_晚上加班,周二_早上_出勤,周二_早上_迟到,周二_早上_旷工,周二_早上_早退,周二_下午_出勤,周二_下午_迟到,周二_下午_旷工,周二_下午_早退,周二_晚上加班,周三_早上_出勤,周三_早上_迟到,周三_早上_旷工,周三_早上_早退,周三_下午_出勤,周三_下午_迟到,周三_下午_旷工,周三_下午_早退,周三_晚上加班,周四_早上_出勤,周四_早上_迟到,周四_早上_旷工,周四_早上_早退,周四_下午_出勤,周四_下午_迟到,周四_下午_旷工,周四_下午_早退,周四_晚上加班,周五_早上_出勤,周五_早上_迟到,周五_早上_旷工,周五_早上_早退,周五_下午_出勤,周五_下午_迟到,周五_下午_旷工,周五_下午_早退,周五_晚上加班"
g.Caption = "总计"
t.SubtotalGroups.Add(g)

t.Subtotal()
DataTables("统计测试").ResumeRedraw

 老师没看懂您的逻辑，我运行了一下结果有点不对：

代码不用改，设置一下下面

 

此主题相关图片如下：qq截图20171210143851.png

明白了，谢谢老师！厉害！