Conversion failed when converting date and/or time from character string SQL Server stored procedure

Question

While trying to execute the following procedure from a C# Winforms project I am getting the following error:

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

This is my stored procedure:

CREATE PROCEDURE DisplayTime  
    @name VARCHAR(50),  
    @fromdate date,    
    @todate date  
AS  
BEGIN  
    DECLARE @VTIME  TIME(7)   

    SELECT 
        @VTIME = CAST(SUM(DATEDIFF(SECOND, 0, time)) /60/60 AS TIME)   
            +':'+CAST(SUM(DATEDIFF(SECOND, 0, time)) /60%60 AS TIME)   
            +':'+ CAST(SUM(DATEDIFF(SECOND, 0, time)) % 60 AS TIME)  
    FROM
        (SELECT time 
         FROM time 
         WHERE [user] = '@name' 
           AND date BETWEEN '@fromdate' AND '@todate') AS T

    SELECT @VTIME AS count_down_sec
END

This is my C# code:

private void button1_Click(object sender, EventArgs e)  
{  
    if (txt_UserName.Text != "")  
    {  
        con.Open();  
        cmd = new SqlCommand("DisplayTime",con);  
        cmd.CommandType = CommandType.StoredProcedure;  

        cmd.Parameters.AddWithValue("@name", txt_UserName.Text);  
        cmd.Parameters.AddWithValue("@fromdate",dateTimePicker1.Value.Date);  
        cmd.Parameters.AddWithValue("@todate",dateTimePicker2.Value.Date);  

        cmd.ExecuteNonQuery();  
        con.Close();  

        MessageBox.Show(txtTotal.Text);  
    }  
}

Don't put quotes around your T-SQL variables, that makes them just strings. — Crowcoder, Feb 03 '18 at 12:09
Can you execute the procedure in sql server management studio? How are you constructing the Command parameters in c# and what are their values? — Crowcoder, Feb 03 '18 at 12:19
You didn't update your T-sql code or say if it would run in SSMS. As `time` is a reserved word, I suggest you don't use it to name everything. — Crowcoder, Feb 03 '18 at 12:54
Possible duplicate of [Conversion failed when converting date and/or time from character string while inserting datetime](https://stackoverflow.com/questions/14119133/conversion-failed-when-converting-date-and-or-time-from-character-string-while-i) — Hasan Fathi, Feb 03 '18 at 13:01
Backup. Start over. What exactly are you trying to accomplish? And start developing good developer habits. Your procedure is poorly named. It has nothing to do with "display" - that is a function of whatever consumes the output. In addition, post DDL for the tables/columns involved and some sample data. And stop using [addwithvalue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — SMor, Feb 03 '18 at 14:03

score 0 · Accepted Answer · answered Feb 05 '18 at 05:06

Not sure what you are trying to do in this procedure. But, parameter names need not to be enclosed in single quotes unless you use Execute(). Remove the quotes around @name, @fromdate and @todate

Also, you will need to come up with proper naming so that, other developers aren't confused with datatype name and column/table names.

CREATE PROCEDURE DisplayTime  
@name VARCHAR(50),  
@fromdate date,    
@todate date  
AS  
BEGIN  
DECLARE @VTIME  TIME(7)   

SELECT 
    @VTIME = CAST(SUM(DATEDIFF(SECOND, 0, time)) /60/60 AS TIME)   
        +':'+CAST(SUM(DATEDIFF(SECOND, 0, time)) /60%60 AS TIME)   
        +':'+ CAST(SUM(DATEDIFF(SECOND, 0, time)) % 60 AS TIME)  
FROM
    (SELECT time 
     FROM time 
     WHERE [user] = @name
       AND date BETWEEN @fromdate AND @todate) AS T

SELECT @VTIME AS count_down_sec
END

Conversion failed when converting date and/or time from character string SQL Server stored procedure

1 Answers1