vb.net 将 dd/MM/yyyy 输入转换为日期格式的 yyyy-MM-dd，而不是 varchar/datetime
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23194700/
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
Converting dd/MM/yyyy input to yyyy-MM-dd in date format not varchar/datetime
提问by user2552331
After searching for a full day, I'm unable to find a solution, facing multiple conversion errors. Main objective is to convert a dd/MM/yyyyto yyyy-MM-dd"date" format to fit in SQL Server.
搜索了一整天后，我无法找到解决方案，面临多个转换错误。主要目标是将转换dd/MM/yyyy到yyyy-MM-dd“日期”格式，以适应在SQL Server中。
I have a SQL Server 2012 database with datatype dateas a column in my table.
我有一个 SQL Server 2012 数据库，其数据类型date作为表中的一列。
I'm using a jQuery datepicker with jquery-1.4.1-vsdoc.js on my project website that outputs dd/MM/yyyywhich is obtained using
我在我的项目网站上使用带有 jquery-1.4.1-vsdoc.js 的 jQuery datepicker 输出dd/MM/yyyy使用
Dim dDate As DateTime = DateTime.Parse(Request.Form(txtStart.UniqueID))
Then I'm attempting to do an insert SQL using the date obtained but the database date format is yyyy-MM-dd.
然后我尝试使用获得的日期执行插入 SQL，但数据库日期格式为yyyy-MM-dd.
Have tried this but it doesn't work:
试过这个，但它不起作用：
SELECT CONVERT(date, CONVERT(DATETIME, '14/10/2011', 0), 120)
Tried this and it works but it's of varchardata type which is not what I need
试过了，它可以工作，但它的varchar数据类型不是我需要的
SELECT REPLACE(CONVERT(VARCHAR(10), '15/4/2014', 111), '/', '-') AS [YYYY-MM-DD]
I really need a code either on my website VB/C# code or SQL statement whichever to fulfill this please help
我真的需要在我的网站 VB/C# 代码或 SQL 语句上的代码来实现这一点，请帮助
My script using the jQuery has already written in code the format that my sql is currently set, however at runtime it does not apply
我使用 jQuery 的脚本已经用代码编写了我的 sql 当前设置的格式，但是在运行时它不适用
<script type="text/javascript">
$(document).ready(function () {
$("#<%=txtStart.ClientID %>").dynDateTime({
showsTime: false,
ifFormat: "%Y-%m-%d ",
daFormat: "%l;%M %p, %e %m, %Y",
align: "BR",
electric: false,
singleClick: false,
displayArea: ".siblings('.dtcDisplayArea')",
button: ".next()"
});
});
</script>
回答by Jon Skeet
Main objective is to convert a dd/MM/yyyy to yyyy-MM-dd "date" format to fit in SQL
主要目标是将 dd/MM/yyyy 转换为 yyyy-MM-dd“日期”格式以适应 SQL
Don't! You should avoid string conversions as far as you possibly can. Keep your data in its natural representation for as much of the time as you can.
别！您应该尽可能避免字符串转换。尽可能多地将数据保持在其自然表示中。
You may well need to parse the date from dd/MM/yyyyto DateTimeto start with (although if you can avoid even this, do so - we don't know where your data comes from), but then you should leaveit as a DateTime. Use parameterized SQL instead, passing the value of the parameter to the database as a DateTime. Of course, you should alsomake sure that the fields in your database are using the appropriate type to start with (not varchar). Ensuring that your schema matches your data is crucial to running a sane system.
您可能需要解析的日期从dd/MM/yyyy到DateTime一起开始（虽然如果你能避免连这个，这样做-我们不知道你的数据来自），但你应该离开它作为一个DateTime。请改用参数化 SQL，将参数值作为DateTime. 当然，您还应该确保数据库中的字段使用适当的类型开头（而不是varchar）。确保您的架构与您的数据匹配对于运行一个健全的系统至关重要。
The parsing part should be relatively simple. For example:
解析部分应该比较简单。例如：
DateTime date = DateTime.ParseExact(text, "dd/MM/yyyy",
CultureInfo.InvariantCulture);
You maywant to use a user's culture instead (depending on where the data has come from) and you maywant to use DateTime.TryParseExactinstead (if this is user-entered data rather than something from machine-to-machine communication).
您可能想要使用用户的文化（取决于数据的来源），并且您可能想要使用DateTime.TryParseExact（如果这是用户输入的数据而不是机器对机器通信的数据）。
回答by Sudhakar Tillapudi
You have to remember that there is no format for DateTime.if you want convert some date string into DateTime and if you know the Date format upfront you can use DateTime.ParseExact()
您必须记住，DateTime如果要将某些日期字符串转换为 DateTime 并且您预先知道日期格式，则可以使用DateTime.ParseExact()
Try This:
尝试这个：
string strDate = "21/04/2014";
DateTime dt = DateTime.ParseExact(strDate,"dd/MM/yyyy",
CultureInfo.InvariantCulture);
now you can store the DateTime variable dtinto database but if you want to display the DateTime in some format ex:yyyy-MM-dd you need to call ToString()as below
现在您可以将 DateTime 变量存储dt到数据库中，但是如果您想以某种格式显示 DateTime，例如：yyyy-MM-dd，您需要调用ToString()如下
String datetime = dt.ToString("yyyy-MM-dd");
回答by Nayeem Mansoori
Use the dateFormatoption
使用dateFormat选项
$(function(){
$("#to").datepicker({ dateFormat: 'yy-mm-dd' });
$("#from").datepicker({ dateFormat: 'yy-mm-dd' }).bind("change",function(){
var minValue = $(this).val();
minValue = $.datepicker.parseDate("yy-mm-dd", minValue);
minValue.setDate(minValue.getDate()+1);
$("#to").datepicker( "option", "minDate", minValue );
})
});
DEMO here
演示在这里
回答by Himansz
we can try ParseExact method while using InvariantCulture. This provides information about a specific culture. The information includes the names for the culture,the writing system( yyyy-MM-dd in your case) etc.
我们可以在使用 InvariantCulture 的同时尝试 ParseExact 方法。这提供了有关特定文化的信息。该信息包括文化名称、书写系统（在您的情况下为 yyyy-MM-dd）等。
String MyDateString;
DateTime MyDateTime;
MyDateString = txtDateOfIncidence.Text.Trim()+ " " + txtTiming.Text.Trim();
MyDateTime = DateTime.ParseExact(MyDateString, "yyyy/MM/dd" + " hh:mm tt", System.Globalization.CultureInfo.InvariantCulture);
