conversion failed when converting date/or time from character string.

Question

1.00/5 (1 vote)

See more:

Performing SUM over a column of datatype TIME

column name " E-Run Time "

C#

 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();

Getting exception for following line:

DateTime emptiTime = (DateTime)cmd3.ExecuteScalar();

Quote:
Conversion failed when converting date and/or time from character string.

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.

Posted 4-Feb-16 4:09am

peoria123

Updated 4-Feb-16 4:37am

CHill60

v2

Add a Solution

Comments

jaket-cp 4-Feb-16 10:32am

maybe try putting the column name in square brackets like so [E-Run Time], it may help.

Have a read of:
https://technet.microsoft.com/en-us/library/ms176027(v=sql.105).aspx

2 solutions

Solution 1

As mentioned in the comments, you need to use square brackets to enclose your column names, not single quotes:

SQL

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

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.

Posted 4-Feb-16 4:56am

Richard Deeming

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2016-02-04T04:58:00

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

C#

DateTime emptiTime = Convert.ToDateTime(cmd3.ExecuteScalar());

or better

DateTime emptiTime = DateTime.Parse(cmd3.ExecuteScalar().ToString());

or my personal favourite

C#

//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