Avatar of doramail05
doramail05
Flag for Malaysia

asked on 

Call UDF from C# with DateTime parameter

having udf which will getting DateTime Parameter and return an int,

it has error : Incorrect syntax near '16'.


public static int GetNoOfMonth(DateTime dtDateofthemonth)
    {
        string strConnString = connstring.getIDashString();

        SqlConnection con = new SqlConnection(strConnString);
        con.Open();
        SqlCommand com = new SqlCommand("Execute dbo.udf_GetNumDaysInMonth(" + dtDateofthemonth.ToShortDateString() + ")", con);
        
        return (int)com.ExecuteScalar();
        con.Close();


    }

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO

Open in new window

C#Microsoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
doramail05

8/22/2022 - Mon