sql server - Datetime issues with vb.net and MSSQL

Question

I am been creating a desktop form in vb.net in which I used datetime-picker tool. I want a format of dd/MM/yyyy so I kept that format from the datetimepicker properties.

But when I am trying to insert the records from vb.net to MSSQL, it will obvious shows me SQL Exception:

conversion failed when converting date and/or time from character string.

because MSSQL supports some ISO FORMAT and all.(MM/dd/yyyy, accepted and ran properly,I tried this).

So I thought to convert the date into vb.net, store it in a variable declared as 'date' or 'datetime' (tried both), into accepted format and inserted the variable into the Sql Command.

The following command I used to convert date.

1) Fdt = Date.ParseExact(From_Dt.Text, "MM/dd/yyyy", Globalization.CultureInfo.InvariantCulture)-------- Searched the internet and got this stuff.

2) Fdt = Convert.ToDateTime(From_Dt.Text)

3) #" & format(DateTimePicker1.Value.Date) & "# ----directly inside the sqlcommand in vb.net.

and many other functions I am not posting now.

I am not getting one thing, all above worked and gave me result in MM/dd/yyyy (checked by adding watch on it during runtime) but it still gives me same exception but if I change the date format from datetimepicker properties to MM/dd/yyyy then the sqlquery is accepted without exception..

Even I tried datatype of the field to date/datetime/datetime2(7) one by one.

And even tried to convert directly into SQLQuery by some Convert/Cast function but the same exception I got.

But none of them worked, I had referred many links and this question may be similar to other but I didn't found any solution so I at last had to create this question.

The Code in which query is fired :

Try
        'Dim Fdt As DateTime, Tdt As DateTime
        'Fdt = Date.ParseExact(From_Dt.Text, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture)
        'Tdt = Date.ParseExact(To_Dt.Text, "yyyy-MM-dd", Globalization.CultureInfo.InvariantCulture)
        'Fdt = Convert.ToDateTime(From_Dt.Text)
        'Tdt = Convert.ToDateTime(To_Dt.Text)
        If txtCompName.Text <> nothing And cmbCompType.Text <> nothing Then
            If CheckEof("select * from Company where Comp_Year = '" & Year1 & "' and Comp_Code = '" & txtCompCode.Text & "'", con) Then
                cmd = New SqlCommand(("insert into Company (Comp_Year, Comp_Code, Comp_Name, Comp_Prop, Comp_Add1, Comp_Add2, Comp_City, Comp_Phone, Comp_FAX, Comp_GST, Comp_CST, Comp_PNR, Comp_TDSNo, Comp_DrugLIC1, Comp_DrugLIC2, Comp_Mess1, Comp_Mess2, Comp_FDT, Comp_TDT, Comp_Distribution, Comp_Juridiction, Comp_Type) values('" &
                Year1 & "','" & txtCompCode.Text & "','" & txtCompName.Text & "','" & txtCompShtName.Text & "','" &
                txtCompAdd1.Text & "','" & txtCompAdd2.Text & "','" & txtCompCity.Text & "','" & txtCompPhno.Text & "','" &
                txtCompMobile.Text & "','" & txtCompGST.Text & "','" & txtCompCST.Text & "','" & txtCompPAN.Text & "','" &
                txtCompTDS.Text & "','" & txtCompDrg1.Text & "','" & txtCompDrg2.Text & "','" & txtCompMsg1.Text & "','" &
                txtCompMsg2.Text & "','" & From_Dt.Value & "','" & To_Dt.Value & "','" & txtCompDist.Text & "','" &
                txtCompJuri.Text & "','" & cmbCompType.Text & "')"), con)
            End If
            ExecuteQuery()
            MsgBox("Company Created SuccessFully,", MessageBoxIcon.Information)
            CompCreation_Reset()
        Else
            MsgBox("Mandatory fields cannot be Blank", MessageBoxIcon.Stop)
            txtCompName.Focus()
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try

Answer 1

Winforms DateTimePicker has a datetime type property called Value

.Net Datetime maps directly to sql server datetime, and since datetime stores no display format, you don't need to worry about the presentation layer's format at all.

simply pass the value property as a parameter to your sql statement. Further reading: How do I create a parameterized SQL query? Why Should I?