是否有方法更改新DateTime (VB.NET，Access)的格式

Question

我正在利用Access数据库存储记录的数据。目前，我正在返回0，但是我知道下面的子变量大约有五次出现。我正在使用下面的潜艇返回某些记录在某一个月中发生的次数：

Public Sub CountAddedDIM() Handles MyBase.Load
        '***EDIT THESE DATES, DIFFERENT FORMATTING POSSIBLE?***
        Dim logDates() As DateTime = {New DateTime(1, 1, 2021),
                                      New DateTime(2, 1, 2021),
                                      New DateTime(3, 1, 2021),
                                      New DateTime(4, 1, 2021),
                                      New DateTime(5, 1, 2021),
                                      New DateTime(6, 1, 2021),
                                      New DateTime(7, 1, 2021),
                                      New DateTime(8, 1, 2021),
                                      New DateTime(9, 1, 2021),
                                      New DateTime(10, 1, 2021),
                                      New DateTime(11, 1, 2021),
                                      New DateTime(12, 1, 2021)}

        ' set 0,0
        PrintsAndShopCHRT.Series("Added Missing Dimension").Points.AddXY(0, 0)

        Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb")
            connection.Open()

            For counter As Integer = 0 To logDates.Count - 1
                Dim sql = $"SELECT COUNT(*) FROM DataCollection WHERE [ADDED MISSING DIMENSION] = 'Added Missing Dimension' AND [M/Y OF LOG] = @MY_OF_LOG;"
                Using Command As New OleDbCommand(sql, connection)
                    Command.Parameters.Add("@MY_OF_LOG", OleDbType.Date).Value = logDates(counter)
                    Dim returnValue = Command.ExecuteScalar()

                    Dim count As Integer = Integer.Parse(returnValue.ToString)

                    ' other points
                    PrintsAndShopCHRT.Series("Added Missing Dimension").Points.AddXY(counter + 1, count)
                End Using
            Next
        End Using

        ' INSERT CHART SETTINGS
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Minimum = 0.0
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Maximum = 12
        PrintsAndShopCHRT.ChartAreas(0).AxisX.Interval = 1
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Minimum = 0.0
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Maximum = 45
        PrintsAndShopCHRT.ChartAreas(0).AxisY.Interval = 5
    End Sub

当像我一样使用New格式时，我会得到以下错误：

System.ArgumentOutOfRangeException：“年、月和日参数描述了一个无法表示的DateTime。”

我知道DateTimes通常被格式化为"YYYY，MM，dd"，但是有没有可能像上面显示的那样格式化它呢？使用月/日/年信息的数据库列使用格式mm/dd/yyyy。

谢谢你的帮助。

Answer 1

DateTime的构造函数有许多重载。显然你使用的是年、月、日。看见

DateTime构造函数

Answer 2

您可以创建一个扩展方法。

Module DateTimeCustomFormatExtensions
<Extension()>
Function ToCustomFormat(ByVal dt As DateTime) As DateTime
    Dim culture = System.Globalization.CultureInfo.CurrentCulture
    Return DateTime.ParseExact(dt.ToString("MM/dd/yyyy"), "MM/dd/yyyy", culture)
End Function
End Module

代码看起来像：

Dim logDates() As DateTime = {New DateTime(2021, 1, 1).ToCustomFormat(),
                                  New DateTime(2021, 2, 1).ToCustomFormat(),
                                  ...}

Answer 3

如您所见，如果您遵循@dbasnett链接。DataTime的构造函数采用(year, month, day)。你把一年放在一天的时间里，所以，当然，这一天应该是1-31。

我还想指出，您应该在循环之外创建一次命令和参数集合。唯一改变的是For循环中参数的值。您不需要.Parse返回的值。我们知道Count总是返回一个Integer (除了MySql返回一个Long)，所以我们只需要用CInt来转换它。

我使用DataPoint列表的唯一原因是我不知道如何在For循环中操作Series。我想展示归还的东西。.AddXY可以接受一个DataPoint数组。如果您在这里遇到错误，只需更改为

Chart1.Series("Added Missing Dimension").Points.AddXY(ChartPoints.ToArray)

代码未被测试。

Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim logDates() As DateTime = {New DateTime(2021, 1, 1),
                                  New DateTime(2021, 2, 1),
                                  New DateTime(2021, 3, 1),
                                  New DateTime(2021, 4, 1),
                                  New DateTime(2021, 5, 1),
                                  New DateTime(2021, 6, 1),
                                  New DateTime(2021, 7, 1),
                                  New DateTime(2021, 8, 1),
                                  New DateTime(2021, 9, 1),
                                  New DateTime(2021, 10, 1),
                                  New DateTime(2021, 11, 1),
                                  New DateTime(2021, 12, 1)}
    Dim sql = $"SELECT COUNT(*) FROM DataCollection WHERE [ADDED MISSING DIMENSION] = 'Added Missing Dimension' AND [M/Y OF LOG] = @MY_OF_LOG;"
    Dim ChartPoints As New List(Of DataPoint)
    Using connection As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\Database1.accdb"),
            Command As New OleDbCommand(sql, connection)
        Command.Parameters.Add("@MY_OF_LOG", OleDbType.Date)
        connection.Open()
        For counter As Integer = 0 To logDates.Count - 1
            Command.Parameters("@MY_OF_LOG").Value = logDates(counter)
            Dim count = CInt(Command.ExecuteScalar())
            ChartPoints.Add(New DataPoint(counter + 1, count))
        Next
    End Using
    'To see if you are getting the data you expect
    For Each p As DataPoint In ChartPoints
        Debug.Print($"{p.XValue}, {p.YValues}")
    Next
    Chart1.Series("Added Missing Dimension").Points.AddXY(ChartPoints)
End Sub