从字符串转换日期/时间时转换失败。 [英] 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屋！