从字符串转换日期/时间时转换失败。 [英] conversion failed when converting date/or time from character string.
问题描述
对一列数据类型TIME执行SUM
列名E-Run Time
Performing SUM over a column of datatype TIME
column name " E-Run Time "
SqlCommand cmd3 = new SqlCommand("SELECT CONVERT(TIME, DATEADD(s, SUM((DATEPART(hh,'E-Run Time') * 3600 ) + (DATEPART(mi,'E-Run Time') * 60 ) + DATEPART(ss,'E-Run Time')),0))FROM dbo.Table1 ", dbConnection);
DateTime emptiTime = (DateTime)cmd3.ExecuteScalar();
textBox3.Text = emptiTime.ToShortTimeString();
获取以下行的例外:
DateTime emptiTime =(DateTime )cmd3.ExecuteScalar();
Getting exception for following line:
DateTime emptiTime = (DateTime)cmd3.ExecuteScalar();
从字符串转换日期和/或时间时转换失败。
Conversion failed when converting date and/or time from character string.
即使是像DATEPART这样的小型查询,我也会遇到同样的异常。我是否必须添加任何标题?
ExecuteScalar()出错了。
谢谢。
Even for small query like DATEPART I am getting same exception. Do I have to add any header?
something wrong with the ExecuteScalar().
Thank you.
推荐答案
您显示的SQL查询实际上只返回一个时间,因此您不能按照您尝试的方式进行投射。
您可以使用
The SQL query you have shown is actually returning a time only so you can't just cast it in the way you are trying.
You could use
DateTime emptiTime = Convert.ToDateTime(cmd3.ExecuteScalar());
或更好
or better
DateTime emptiTime = DateTime.Parse(cmd3.ExecuteScalar().ToString());
或我个人最喜欢的
or my personal favourite
//using System.Globalization;
DateTime emptiTime;
if (!DateTime.TryParseExact(cmd3.ExecuteScalar().ToString(),
"hh:MM:ss",
CultureInfo.InvariantCulture,
DateTimeStyles.None,
out emptiTime))
{
//handle the error
}
...有点啰嗦但更强大
... a bit more long-winded but more robust
如评论中所述,您需要使用用方括号括起你的列名,而不是单引号:
As mentioned in the comments, you need to use square brackets to enclose your column names, not single quotes:
SELECT CONVERT(TIME, DATEADD(s, SUM((DATEPART(hh, [E-Run Time]) * 3600 ) + (DATEPART(mi, [E-Run Time]) * 60 ) + DATEPART(ss, [E-Run Time])), 0))FROM dbo.Table1
就目前而言,您查询当前正在尝试转换文字字符串E-Run Time
到 datetime
值,这显然不起作用。
你的查询将结果转换为 TIME
值,但您的C#代码尝试将其读取为 DateTime
值。那不行;您需要将其作为 TimeSpan
来阅读。
日期/时间数据类型和参数 [ ^ ]
此外,您应该考虑为列提供更好的名称。避免使用空格,坚持拉丁字符和数字 - A..Z
和 0..9
- 并确保名字以字母开头。这样,您就不需要在使用它们之前将名称包装在方括号中。
As it stands, you query is currently trying to convert the literal string "E-Run Time"
to a datetime
value, which obviously won't work.
Your query converts the result to a TIME
value, but your C# code tries to read it as a DateTime
value. That won't work; you need to read it as a TimeSpan
instead.
Date/Time Data Types and Parameters[^]
Also, you should consider giving your columns better names. Avoid spaces, stick to Latin characters and numbers - A..Z
and 0..9
- and make sure the names start with a letter. That way, you won't need to wrap the names in square brackets before you can use them.
这篇关于从字符串转换日期/时间时转换失败。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!