Visual Studio中的时间间隔

Question

我请求你帮助我获得"quarter"在我的小项目中的一年。

VB。NET项目是"日常工作完成"。在公司里。我正在使用Visual Studio 2010 - windows应用程序- SQL Server 2012，我想查看"工作"。发生在每年的第一季度，第二季度，……

从gridview的组合框中选择。请帮

====下面的代码是我试图执行的函数====

Public Sub Load_WorkDayTable_By_period(ByVal DateWork As Date)
WorkDayTable.Clear()
Dim cmd As New SqlCommand(" select (@DateWork % 100 - 1) / 3 + 1 as [Quarter] From WorkDayTable ", connSQLServer) 

cmd.Parameters.Add("DateWork", SqlDbType.Date).Value = DateWork

connSQLServer.Open()
WorkDayTable.Load(cmd.ExecuteReader)
connSQLServer.Close()
cmd = Nothing
End Sub

==========这是调用函数

的子节点

Private Sub Com_viewper_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles Com_viewper.SelectedIndexChanged
If Com_viewper.SelectedItem = "الربع الأول" Then
load_WorkDayTable()
Load_WorkDayTable_By_period(Com_viewper.SelectedValue)
DataGridView1.DataSource = WorkDayTable
With DataGridView1
.DataSource = WorkDayTable
.Columns("EmpName").HeaderText = "اسم الشركة"
.Columns("EmpName").Width = 150
.Columns("Workk").HeaderText = "الموظف"
.Columns("Workk").Width = 100
.Columns("DateWork").HeaderText = "رقم الهاتف"
.Columns("DateWork").Width = 100
.Columns("NoteWork").HeaderText = "البريد الالكتروني"
.Columns("NoteWork").Width = 100
.Columns("WorkNum").HeaderText = "رقم العمل"
.Columns("WorkNum").Width = 100
.Columns("flagg").HeaderText = "مؤشر"
.Columns("flagg").Width = 120
.Columns("EmpNum").HeaderText = "رقم الموظف"
.Columns("EmpNum").Width = 120
.Columns("Worki").HeaderText = "عمل"
.Columns("Worki").Width = 100
.Columns("Workii").HeaderText = "عمل"
.Columns("Workii").Width = 100
.Columns("Workc").HeaderText = "عمل"
.Columns("Workc").Width = 100
.Columns("Workcc").HeaderText = "عمل"
.Columns("Workcc").Width = 100
.Columns("WorkD").HeaderText = "عمل"
.Columns("WorkD").Width = 100
End With
End If
End Sub

Answer 1

EndDate加1，因为日期是12 AM。你可能需要稍微摆弄一下。我不得不猜测你的WorkDayTable中日期字段的名称。

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
ComboBox1.Items.AddRange({"First Quarter", "Second Quarter", "Third Quarter", "Fourth Quarter"})
End Sub
Private Sub ComboBox1_SelectionChangeCommitted(sender As Object, e As EventArgs) Handles ComboBox1.SelectionChangeCommitted
Dim StartDate As Date
Dim EndDate As Date
Dim CurrentYear As Integer = Now.Year
Select Case ComboBox1.SelectedItem.ToString
Case "First Quarter"
StartDate = New Date(CurrentYear, 1, 1)
EndDate = New Date(CurrentYear, 4, 1)
Case "Second Quarter"
StartDate = New Date(CurrentYear, 4, 1)
EndDate = New Date(CurrentYear, 5, 1)
Case "Third Quarter"
StartDate = New Date(CurrentYear, 7, 1)
EndDate = New Date(CurrentYear, 10, 1)
Case "Fourth Quarter"
StartDate = New Date(CurrentYear, 10, 1)
EndDate = New Date(CurrentYear + 1, 1, 1)
Case Else
MessageBox.Show("Please select a quarter")
Exit Sub
End Select
Debug.Print($"{StartDate}, {EndDate}")
Load_WorkDayTable_By_period(StartDate, EndDate)
End Sub
Private ConStr As String = "Your connection string"
Private WorkDayTable As New DataTable
Public Sub Load_WorkDayTable_By_period(StartDate As Date, EndDate As Date)
WorkDayTable.Clear()
Using connSQLServer As New SqlConnection(ConStr),
cmd As New SqlCommand(" select * From WorkDayTable Where WorkDate Between @StartDate And @EndDate;", connSQLServer)
cmd.Parameters.Add("@StartDate", SqlDbType.Date).Value = StartDate
cmd.Parameters.Add("@EndDate", SqlDbType.Date).Value = EndDate
connSQLServer.Open()
Using reader = cmd.ExecuteReader
WorkDayTable.Load(reader)
End Using
End Using
End Sub

Answer 2

我倾向于通过获取所需年份的第一天，然后添加适当的月份数来指定一个季度，例如

Private Function GetRecordsByQuarter(year As Integer, quarter as Integer) As DataTable
Dim firstDayOfYear As New Date(year, 1, 1)
Dim inclusiveStartDate = firstDayOfYear.AddMonths(3 * (quarter - 1))
Dim exclusiveEndDate = inclusiveStartDate.AddMonths(3)
Using adapter As New SqlDataAdapter("SELECT * FROM MyTable WHERE CreatedDate >= @StartDate AND CreatedDate < @EndDate",
connectionString)
With adapter.SelectCommand.Parameters
.Add("@StartDate", SqlDbType.DateTime2).Value = inclusiveStartDate
.Add("@EndDate", SqlDbType.DateTime2).Value = exclusiveEndDate
End With
Dim table As New DataTable
adapter.Fill(table)
Return table
End Using
End Function