SQL Server日期时间和C#的DateTime [英] SQL server DateTime and C# DateTime
问题描述
在我的SQL Server我有测试,其中只包含三行一个非常简单的表:
ID
,日期
和小时
。( VARCHAR
,的DateTime
, VARCHAR
)
在SQL的DateTime格式是这样的: YYYY-MM -dd HH:MM:SS.FFF
在C#中的DateTime格式是这样的: YYYY-DD- MM HH:MM:SS.FFF
我用下面的代码来获得C#DateTime格式:
字符串DDD =2012年10月10日00:00:00.000
DT1 = DateTime.ParseExact(DDD,YYYY-MM-DD HH:MM:SS.FFF,NULL);
如果我试图把它变成 YYYY-MM-DD
我得到一个错误:
日期时间,这是由字符串表示,不是由
支持压延。
块引用>
在我的C#应用程序正在试图寻找的破甲日期之间的时间总量。我是那种得到了工作,但它仅适用于01和12之间的日期
因此,像从
2012-01-10
到二○一二年十月十日
(TEN天)会给我从数据库总学时的正确的金额。
但是,当我写
2012-01-10
到2012-14-10
(14天)我得到一个错误:
一个char数据类型到datetime数据类型的转换导致
在一个不折不扣的-range
块引用>
感谢您提前。
PS。你能提出一个更简单的方法来获得的日期?
MySQL的查询
字符串的CommandText =SELECT * FROM date_test WHERE ID ='4'和日之间'+ DT1 +'和'+ DT2 +';
我已想出问题,但不是解决办法。
的问题是,SQL数据库着眼于格式,并希望YYYY-MM-DD,但是C#只能发送YYYY-MM-DD。
为什么不能
ParseExact()
做YYYY-MM-DD?
解决方案
日期时间,这是由字符串表示,不被压光机的支持。
块引用>
正考虑该错误
,因为你的C#应用程序视图的日期2012 -14-10
的话说14
月10
一天,2012th年
。一天和一年的工作发现,但当月没有。此外,不要试图改变你的C#应用程序是如何看待的日期,这是基于该系统的文化。
你混淆如何的定义的一个
的DateTime
对象以及如何的显示的之一。
既然你'重新存储您的日期作为
的DateTime
在SQL中,有一个很好的理由让我相信你需要做的任何一种解析不。 。请考虑下面的代码示例VAR = dataTable的新的DataTable();
变种的DataAdapter =新的SqlDataAdapter(SELECT * FROM YourTable,{连接字符串});
DataAdapter.Fill方法(dataTable的);
VAR yourDate = dataTable.Rows [0] [日]; < ===类型将日期时间,简单。
添加参数
让我们以你的榜样查询:
SELECT * FROM date_test WHERE ID ='4'和日之间'+ DT1 +'和'+ DT2 +';
块引用>
和我们进行了修复它一下,考虑下面的例子:
VAR = dataTable的新的DataTable();
变种的DataAdapter =新的SqlDataAdapter(SELECT * FROM date_test WHERE ID = @ID和日期间@StartDate和@EndDate,{连接字符串});
dataAdapter.SelectCommand.Parameters.AddWithValue(@ ID,4);
dataAdapter.SelectCommand.Parameters.AddWithValue(@起始日期,新的datetime(2012,10,1));
dataAdapter.SelectCommand.Parameters.AddWithValue(@结束日期,新的datetime(2012,10,14));
DataAdapter.Fill方法(dataTable的);
VAR yourDate = dataTable.Rows [0] [日]; < ===类型将日期时间,简单。
On my SQL server I have a very simple table for testing, which only contains three rows:
ID
,Date
andHours
.(varchar
,DateTime
,varchar
).The DateTime format in SQL is like:
yyyy-MM-dd HH:mm:ss.fff
.The DateTime format in C# is like:
yyyy-dd-MM HH:mm:ss.fff
.I use the following code to get C# DateTime format:
string ddd = "2012-10-10 00:00:00.000"; dt1 = DateTime.ParseExact(ddd, "yyyy-MM-dd HH:mm:ss.fff", null);
If I try to make it into
yyyy-dd-MM
I get an error:The DateTime, which is represented by the string, isn't supported by the calender.
In my C# application is am trying to find total amount of hours between sunder dates. I sort of got it to working, but it only works for dates between 01 and 12.
So like from
2012-01-10
to2012-10-10
(ten days) will give me the correct amount of total hours from the database.But when I write
2012-01-10
to2012-14-10
(fourteen days) I get an error:The conversion of a char data type to a datetime data type resulted in an out-of-range"
Thank you in advance.
PS. Can you suggest a easier way to get dates?
mySQL query
string CommandText = "SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'";
I have figured out the problem but not the solution.
The problem is that SQL database looks at the format, and wants yyyy-MM-dd, but C# can only send yyyy-dd-MM.
Why cannot
ParseExact()
do yyyy-MM-dd?解决方案The DateTime, which is represented by the string, isn't supported by the calender.
This error is being given because your C# application views the date
2012-14-10
as saying the14th
month,10th
day, and2012th year
. The day and year work find, but the month doesn't. Further, don't try and change how your C# application views the date, that's based off the culture of the system.You're confusing how to define a
DateTime
object and how to display one.Since you're storing your date as a
DateTime
in SQL, there's not a good reason for me to believe that you would need to do any kind of parsing. Consider the below code sample.var dataTable = new DataTable(); var dataAdapter = new SqlDataAdapter("SELECT * FROM YourTable", "{connection string}"); dataAdapter.Fill(dataTable); var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.
Adding Parameters
Let's take your example query:
"SELECT * FROM date_test WHERE id = '4' AND date BETWEEN '" + dt1 + "' AND '" + dt2 + "'";
And let's fix it a bit, consider the below example:
var dataTable = new DataTable(); var dataAdapter = new SqlDataAdapter("SELECT * FROM date_test WHERE id = @ID AND date BETWEEN @StartDate AND @EndDate", "{connection string}"); dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", "4"); dataAdapter.SelectCommand.Parameters.AddWithValue("@StartDate", new DateTime(2012, 10, 1)); dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", new DateTime(2012, 10, 14)); dataAdapter.Fill(dataTable); var yourDate = dataTable.Rows[0]["Date"]; <=== the type will be DateTime, simple.
这篇关于SQL Server日期时间和C#的DateTime的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!