使用 vb.net dd/MM/yyyy 在 sql 中插入日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26199440/
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
Inserting date in sql using vb.net dd/MM/yyyy
提问by MAX
I have a problem with date, when I insert a date into my sql database using datetimepicker keeps saying there is a problem converting string to date, I am using the following format dd/MM/yyyy I know that sql supports only MM/dd/yyyy and yyyy/MM/dd I want to compare it with function today, what I want is, is there any way to show the user this format dd/MM/yyyy and save in sql using this format MM/dd/yyyy and when comparing with function today I want to compare using this format MM/dd/yyyy???
我有日期问题，当我使用 datetimepicker 将日期插入我的 sql 数据库时，一直说将字符串转换为日期时出现问题，我使用以下格式 dd/MM/yyyy 我知道 sql 仅支持 MM/dd/ yyyy 和 yyyy/MM/dd 我今天想将它与函数进行比较，我想要的是，有什么方法可以向用户显示这种格式 dd/MM/yyyy 并使用这种格式 MM/dd/yyyy 以及何时保存在 sql 中与今天的函数比较 我想使用这种格式 MM/dd/yyyy 进行比较？？？
I am using visual basic and sql server
我正在使用 Visual Basic 和 sql server
回答by Joel Coehoorn
Let's put a couple things together. First:
让我们把一些东西放在一起。第一的：
using datetimepicker
使用日期时间选择器
The DateTimePicker control has a Valuepropertythat gives you a VB DateTime value.
DateTimePicker 控件具有为您提供 VB DateTime 值的Value属性。
We also have this:
我们还有这个：
You are storing dates as dates right? And not strings?
Yeah as dates
您将日期存储为日期对吗？而不是字符串？
是的日期
Given those two things, format doesn't matter. If you're evertrying to convert the DateTime value from your control to a string, you're doing it wrong.
鉴于这两件事，格式并不重要。如果您曾经尝试将 DateTime 值从您的控件转换为字符串，那么您就做错了。
I'll use a simple table with a single DateTime column as an example:
我将使用一个带有单个 DateTime 列的简单表作为示例：
Using cn As New SqlConnection("connection string here"), _
cmd As New SqlCommand("INSERT INTO MyTable VALUES (@MyDateTimeColumn)", cn)
cmd.Parameters.Add("@MyDateTimeColumn", SqlDbType.DateTime).Value = DateTimePicker1.Value
cn.Open()
cmd.ExecuteNonQuery()
End Using
At no point this code ever care what format is used for the DateTime, or try to treat it as a string at all. Code that tries to convert a DateTime value to a string hints that you're using a technique for building your sql queries that will be vulnerable to sql injection attacks. Don't do that.
这段代码从不关心 DateTime 使用什么格式，或者尝试将其视为字符串。尝试将 DateTime 值转换为字符串的代码提示您正在使用一种技术来构建容易受到 sql 注入攻击的 sql 查询。不要那样做。
回答by Sumit Gupta
If you are using DateTimedatatype in SQL, that you mention you did too, then in VB, try to convert your DateTimePicker date and convert it to DateTime Variable using DateTime.TryParse(http://msdn.microsoft.com/en-us/library/9h21f14e(v=vs.110).aspx)
如果您DateTime在 SQL中使用数据类型，您也提到过，那么在 VB 中，尝试使用DateTime.TryParse（http://msdn.microsoft.com/en-us/library/9h21f14e）转换您的 DateTimePicker 日期并将其转换为 DateTime 变量(v=vs.110).aspx)
In this method your can define the incoming format/culture to use to try to convert stringTo DateTimetype, and then use that variable as SQL Parameter to your insert query.
在此方法中，您可以定义传入格式/文化以用于尝试转换string为DateTime类型，然后将该变量用作插入查询的 SQL 参数。
回答by AiApaec
If you're using datetime in your DB and you are sending a datetime from .net then there isn't problem. Maybe the problem is when you parse from datepicker, for example if you have '25/01/2014' it's January but if you try parse without specify the format you can get an error, .net could try to convert using the format 'mm/dd/yyyy'. If you use 'dd/mm/yyyy' you must use this:
如果您在数据库中使用日期时间并且从 .net 发送日期时间，则没有问题。也许问题是当您从 datepicker 解析时，例如，如果您有“25/01/2014”，那是一月，但如果您尝试解析而不指定格式，您可能会收到错误，.net 可能会尝试使用格式“mm”进行转换/dd/yyyy'。如果你使用 'dd/mm/yyyy' 你必须使用这个：
DateTime aDate = DateTime.ParseExact("25/01/2014", "dd/MM/yyyy", null); //this is c#
No problem if your DB receives a datetime (sql type) and you send aDate, for example:
如果您的数据库收到日期时间（sql 类型）并发送日期，则没问题，例如：
using (SqlConnection con = new SqlConnection("connectionString"))
{
using (SqlCommand cmd = new SqlCommand("aCommand", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@aDateTimeParameter", SqlDbType.DateTime).Value = aDate;
}
}
