David David - 2 months ago 14
Vb.net Question

Change format of DateTime value

Everybody seems to hate

DateTime
values.

In my project, I have a
SQL SELECT
query to find results in the database matching the parameter values.

The code I have is:

Dim where As String = "WHERE [Supp_Code] = @scode " & _
"AND [DateFrom] = @datfrom " & _
"AND [DateTo] = @datto " & _
"AND [Comm_Code] = @ccode " & _
"AND [Customer_Code] = @custcode "

Dim sql As String = "SELECT [Comm_Code], [AqYear] FROM [Acquisition Commission] "

sql &= where & " ORDER BY [AqYear] ASC"

Dim cmd As New OleDb.OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("@scode", cmbSupp.Text.Trim)
cmd.Parameters.AddWithValue("@datfrom", datFrom.Value)
cmd.Parameters.AddWithValue("@datto", datTo.Value)
cmd.Parameters.AddWithValue("@ccode", txtCommCode.Text)
cmd.Parameters.AddWithValue("@custcode", cmbCustomerCode.Text)

Dim daYear As New OleDb.OleDbDataAdapter(cmd)
Dim dsYear As New DataSet
daYear.Fill(dsYear)


The code itself has no issues. The issue is that the
DataSet
only ever has 0 rows, because the
DateTime
format of
datFrom.Value
(which is a
DateTimePicker
control) is 'MM/dd/yyyy', but in the database it is stored as 'dd/MM/yyyy'.

What's the easiest way of converting it the correct database format before using it as a parameter?

EDIT

Using the comments/answers below, I've adapted my code to the following:

Dim test As DateTime
Dim test2 As DateTime
test = ugDates.ActiveRow.Cells("DateFrom").Value
test = ugDates.ActiveRow.Cells("DateTo").Value

Try
Dim where As String = "WHERE [Supp_Code] = @scode " & _
"AND [DateFrom] = @datfrom " & _
"AND [DateTo] = @datto " & _
"AND [Comm_Code] = @ccode " & _
"AND [Customer_Code] = @custcode "

Dim sql As String = "SELECT DISTINCT [Comm_Code], [AqYear] FROM [Acquisition Commission] "

sql &= where & " ORDER BY [AqYear] ASC"

Dim cmd As New OleDb.OleDbCommand(sql, con)
cmd.Parameters.Add("@scode", OleDbType.VarChar).Value = cmbSupp.Text
cmd.Parameters.Add(New OleDbParameter("@datfrom", OleDbType.Date).Value = test)
cmd.Parameters.Add(New OleDbParameter("@datto", OleDbType.Date).Value = test2)
cmd.Parameters.Add("@ccode", OleDbType.VarChar).Value = txtCommCode.Text
cmd.Parameters.Add("@custcode", OleDbType.VarChar).Value = cmbCustomerCode.Text


But, it gives me the following error on the second parameter:


The OleDbParameterCollection only accepts non-null OleDbParameter type objects, not Boolean objects.

Answer

The issue is that the DataSet only ever has 0 rows, because the DateTime format of datFrom.Value (which is a DateTimePicker control) is 'MM/dd/yyyy', but in the database it is stored as 'dd/MM/yyyy'.

That is probably NOT the issue unless you are storing the date as a string. A date is a structure, not a string, and inherently does not have formatting. Formatting is something that is applied to a string. Its similar to a number like an int or decimal. If this field is indeed a string you should revisit your schema and update the type accordingly.

Most likely you are trying to compare a date with time either in the value of the parameter OR in the value of the query.

  • Use DateValue function to trim the time of the stored database
  • Use .Date on the date value to only compare the value date

Code:

Dim where As String = "WHERE [Supp_Code] = @scode " & _
        "AND DateValue([DateFrom]) = @datfrom " & _ 
        "AND DateValue([DateTo]) = @datto " & _
        "AND [Comm_Code] = @ccode " & _
        "AND [Customer_Code] = @custcode "


// select either OleDbType.Date or OleDbType.DBDate

cmd.Parameters.Add(new OleDbParameter("@datfrom", OleDbType.Date) With {.Value = datFrom.Value.Date})
cmd.Parameters.Add(new OleDbParameter("@datto", OleDbType.Date) With {.Value = datTo.Value.Date})

The other possible problem is the logic, why not do a between or range check instead of an equality check on DateFrom and DateTo?

"AND DateValue([DateFrom]) >= @datfrom " & _ 
"AND DateValue([DateTo]) <= @datto " & _

Ideally you would use the same value here for both @datfrom and @datto. The query above would be any item that has a start/end date that span the passed in date parameter.