Call UDF from C# with DateTime parameter

doramail05
doramail05 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Director
Commented:
You don't execute a function, you select it e.g. select bo.udf_GetNumDaysInMonth(" + dtDateofthemonth.ToShortDateString() + ")
Dale BurrellDirector

Commented:
Sorry should be select dbo.udf_GetNumDaysInMonth(" + dtDateofthemonth.ToShortDateString() + ")
Anthony Perkins
Top Expert 2012

Commented:
Alternatively convert the function to a Stored Procedure or even better still just use C# for such a trivial exercise and save the roundtrip to the server.
Anthony Perkins
Top Expert 2012

Commented:
If you really have to do it in a UDF, than I suggest you use an inline function and use a tinyint instead of an integer like this:

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] (@myDateTime DATETIME)
RETURNS tinyint
AS
BEGIN
      RETURN DAY(DATEADD(day, -DAY(DATEADD(MONTH, 1, @myDateTime)), DATEADD(MONTH, 1, @myDateTime)))
END
GO


But again, this can and should be accomplished in C#
doramail05

Author

Commented:
ok

using (SqlConnection sqlconn = new SqlConnection(strConnString))
        {
            DataTable dt = new DataTable();
            sqlconn.Open();
            SqlCommand myCmd = new SqlCommand(
                 "select dbo.udf_GetNumDaysInMonth(" + dtDateofthemonth.ToShortDateString() + ")", sqlconn);
            return (int)myCmd.ExecuteScalar();
            sqlconn.Close();

        }

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial