How to pass an optional parameter to the sql function of C #


I have an issue. please help to solve my problem

I have a SQL function

function [dbo].[fnKudishikaAmt]
    (@ParishName nvarchar(100), @Hno int, @dateto datetime = Null)
Returns Decimal(15,2)

This function shows proper result by using the execute command

Select dbo.fnKudishikaAmt('St.George Malankara Catholic Church', 29, default)

My requirement is this function should be called from C#

I am getting the error

Conversion failed when converting datetime from character string


public double kudishikatotal(string ParishName, Int32 HouseNo)
    String SQLText = "select ChurchDB.dbo.fnKudishikaAmt(@ParishName, @Hno, @dateto) as fnresult";

    SqlCommand cmd = new SqlCommand(SQLText);
    cmd.CommandType = CommandType.Text;
    cmd.Parameters.AddWithValue("@ParishName", ParishName);
    cmd.Parameters.AddWithValue("@Hno", HouseNo);
    cmd.Parameters.AddWithValue("@dateto", "default");

    string rval = GetSingleValue(cmd);
    double kudiamt = 0;

    if (rval != null)
        kudiamt = Convert.ToDouble(rval);

    return kudiamt;

private static string GetSingleValue(SqlCommand cmd)
    string ConString = connectionstring();
    string returnvalue = "";

    using (SqlConnection con = new SqlConnection(ConString))
        cmd.Connection = con;
        returnvalue = cmd.ExecuteScalar().ToString();

    return returnvalue;

If you've declared default value for parameter in stored procedure - then you can just not pass this parameter from c# code at all, and in this case it will have default value.

In your case exception thrown because it's impossible to convert string "default" to SqlDateTime which is your parameter type.