Custom format for datetime doesn't work after I change my system time

Question

I am trying to output some SQL queries onto a DataGridView using date constraints from the DateTimePicker

I set my custom date format during form load sub and also in the properties menu from the .vb[design] file.

However, when I run the application it automatically switches the DateTimePicker value from my custom format (yyyy-MM-dd) to Windows system format which is currently set to (m/dd/yy) or whatever format I select in Windows. This is causing problems because the application works when I switch my windows date time format to yyyy-MM-dd, however, if an end-user uses (m/dd/yy) format my application can't grab data from the SQL server.

The SQL Server format is (yyyy-MM-dd)

Private Sub Navs_Load(Sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    DTPTansAct.Value = Today.AddDays(-1)
    DTPTransAct.Format = DateTimePickerFormat.Custom
    DTPTransAct.CustomFormat = "yyyy-MM-dd"
End Sub

Private Sub BtnTransActDL_Click(sender As Object, e As EventArgs) Handles BtnTransActDL.Click
    Dim RecordCount = 0
    Dim Exception = ""

    Dim DLCmd As New MySqlCommand
    Dim DLConn As New MySqlConnection
    DLConn.ConnectionString = connectionstring.text

    Dim DLDA As New MySqlDataAdapter
    Dim DLDT As New DataTable
    Dim bsource As New BindingSource

    Dim SQLString As String = "Select * From table where name like '%" & TxtTransAct.Text & "%' and transactiondate BETWEEN '" & DTPTransAct.Text & "' AND '" & DTPTransAct.Text & "' Limit 5000;"

    Try
        DLConn.Close()
        DLConn.Open()
        DLCmd = New MySqlCommand(SQLString, DLConn)
        DLDA.SelectCommand = DLCmd
        DLDA.Fill(DLDT)
        bsource.DataSource = DLDT
        DGVTransAct.DataSource = bsource
        DLDA.Update(DLDT)
    Catch ex As Exception
        Exception = "ExecQuery Error: " & vbNewLine & ex.Message
    Finally
        DLConn.Close()
        Dim RecordCount1 As String = DLDT.Select().Length
        TextBox16.Text = (RecordCount1.ToString() + " Record(s) Found!")
    End Try
End Sub

There must be something changing the format that you're not showing in your code. But regardless, you shouldn't use `DTPTransAct.Text`. Use `DTPTransAct.Value.ToString("yyyy-MM-dd")` instead. Also, consider using [parameterized queries](https://stackoverflow.com/q/542510/4934172) to prevent sql injection. — 41686d6564 stands w. Palestine, May 16 '18 at 22:06
Another advantage of using parameter is that you don't have to worry about the date format because you pass the date as `Date`, _not `String`_. — 41686d6564 stands w. Palestine, May 16 '18 at 22:12
thank you, i found that at some lines i used `DTPTransAct.Text` instead of `DTPTransAct.Value.ToString("yyyy-MM-dd")` — Anirban, May 22 '18 at 19:34

Custom format for datetime doesn't work after I change my system time

0 Answers0