vb.net 在 SQL Server 2008 中将值从字符串转换为日期格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20140739/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert value from string to date format in SQL Server 2008
提问by Dan
I want to convert date which is stored as string in a SQL Server 2008 database to smalldatetime.
我想将在 SQL Server 2008 数据库中存储为字符串的日期转换为smalldatetime.
The format for the saved string is 16/12/2007and I want to remove / and replace it with - to get proper date format which is 16-12-2007
保存的字符串的格式是16/12/2007，我想删除 / 并将其替换为 - 以获得正确的日期格式16-12-2007
I getting the following error
我收到以下错误
Conversion from string "16/12/2007" to type 'Date' is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidCastException: Conversion from string "16/12/2007" to type 'Date' is not valid.
Source Error:
Line 34: NewsItem.Visible = True
Line 35: NewsItem.Date_Modified = CDate(GetContent.Ndate)
Line 36: NewsItem.Date_Published = CDate(GetContent.Ndate)
从字符串“16/12/2007”到类型“日期”的转换无效。
说明：在执行当前 Web 请求期间发生未处理的异常。请查看堆栈跟踪以获取有关错误及其在代码中的来源的更多信息。
异常详细信息：System.InvalidCastException：从字符串“16/12/2007”到类型“Date”的转换无效。
源错误：
第 34 行：NewsItem.Visible = True
第 35 行：NewsItem.Date_Modified = CDate(GetContent.Ndate)
第 36 行：NewsItem.Date_Published = CDate(GetContent.Ndate)
I thought of creating a function that replace the /character with -to then update the database but it will take long time.
我想创建一个函数来替换/字符，-然后更新数据库，但这需要很长时间。
采纳答案by Lingasamy Sakthivel
Retrieve the date as string from database and then use Date.ParseExact which Converts the specified string representation of a date and time to its DateTime equivalent.
从数据库中以字符串形式检索日期，然后使用 Date.ParseExact 将日期和时间的指定字符串表示形式转换为其等效的 DateTime。
Dim ydate = "16/12/2007"
Dim edate As Date = Date.ParseExact(ydate, "dd/MM/yyyy", System.Globalization.DateTimeFormatInfo.InvariantInfo)
回答by Tim Schmelter
Don't use strings if you actually want to store datetime/smalldatetimes. Use sql-parameters to avoid localization/format issues and - more important - to prevent sql-injection. A VB.NET Datecan be used for a smalldatetimecolumn. Use Date.TryParseto validate and parse the string.
如果您确实想存储datetime/ smalldatetimes，请不要使用字符串。使用 sql 参数来避免本地化/格式问题，更重要的是防止 sql 注入。VB.NETDate可用于smalldatetime列。使用Date.TryParse验证和解析字符串。
Dim sql = "INSERT INTO dbo.TableName(Date_Modified)VALUES(@Date_Modified);SELECT CAST(SCOPE_IDENTITY() AS INT);"
Using con = New SqlConnection("Connection-String")
Using cmd = New SqlCommand(sql, con)
Dim dt As Date
If Not Date.TryParse(GetContent.Ndate, dt) Then
MessageBox.Show("please enter a valid date")
Return
End If
cmd.Parameters.AddWithValue("@Date_Modified", dt)
Dim newID = cmd.ExecuteScalar() ' presuming that you have an identity column that is autoincremented
End Using
End Using
