bonny bonny - 1 year ago 132 Question

Datetime issues with and MSSQL

I am been creating a desktop form in 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 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, 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

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 :

'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
MsgBox("Company Created SuccessFully,", MessageBoxIcon.Information)
MsgBox("Mandatory fields cannot be Blank", MessageBoxIcon.Stop)
End If
Catch ex As Exception
End Try

Answer Source

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?

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download