vb.net 和 MSSQL 的日期时间问题 [英] Datetime issues with vb.net and MSSQL

问题描述

我在 vb.net 中创建了一个桌面表单，我在其中使用了 datetime-picker 工具.我想要一个 dd/MM/yyyy 格式，所以我从 datetimepicker 属性中保留了该格式.

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.

但是当我尝试将记录从 vb.net 插入到 MSSQL 时，很明显会向我显示 SQL 异常:

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.

因为 MSSQL 支持一些 ISO FORMAT 和所有格式.(MM/dd/yyyy，接受并正常运行，我试过了).

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

所以我想将日期转换为 vb.net，将其存储在声明为date"或datetime"的变量中(两者都尝试过)，转换为可接受的格式并将变量插入到 Sql 命令中.

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.

我没有得到一件事，以上都有效，并给了我 MM/dd/yyyy 的结果(通过在运行时添加 watch 来检查)但它仍然给我同样的异常，但如果我从 datetimepicker 属性更改日期格式到 MM/dd/yyyy 然后 sqlquery 被无一例外地接受..

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..

即使我将字段的数据类型一一尝试为 date/datetime/datetime2(7).

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

甚至尝试通过某些 Convert/Cast 函数直接转换为 SQLQuery，但我得到了同样的异常.

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.

触发查询的代码:

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

推荐答案

Winforms DateTimePicker 有一个名为 Value

Winforms DateTimePicker has a datetime type property called Value

.Net 日期时间 直接映射 到 sql server datetime，并且由于 日期时间不存储显示格式，您完全不需要担心表现层的格式.

.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.

只需将 value 属性作为 参数 传递给您的 sql 语句.进一步阅读:如何创建参数化 SQL询问?我为什么要这样做?

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?

这篇关于vb.net 和 MSSQL 的日期时间问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！