Pass datetime parameter in Web API?

When using string concatenation to build your query, it is recommended that you use ISO date format for dates:

In C# we can use the u format specifier to obtain an ISO date for SQL, but it appends the value with a Z, the following is a simple way to use this in strings:

date.ToString("u").TrimEnd('Z');

That is equivalent to:

date.ToString("yyyy-MM-dd HH:mm:ss");

But now we have a problem, does the incoming date parameter represent a whole day, or a specific point in time (for the purposes of this query)? Your request is that we want to specify the Date only, without time, for that we can use this format:

date.ToString("yyyy-MM-dd")

Now we have a new problem, in SQL if your data contains time values, then you need to truncate the data to just the date component, to pickup all the values for the provided date.

So now your query looks like this:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE Cast([Date] as Date) = '"+date.ToString("yyyy-MM-dd")+"'";

Using a function in the where clause makes this a non-SARGable query, which is hard for the database to optimise, you will generally get better performance by using a BETWEEN and passing in a value for the date and the date of the next day:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE [Date] BETWEEN '"+date.ToString("yyyy-MM-dd")+"' AND '"+date.AddDays(1).ToString("yyyy-MM-dd")+"'";

SARGable functions in SQL Server
SARGable is an adjective in SQL that means that an item can be found using an index (assuming one exists). Understanding SARGability can really impact your ability to have well-performing queries. Incidentally – SARGable is short for Search ARGument Able.

But now this query is a bit of a mess, instead we should use parameters to pass through the value, this way we do not need to be aware of ISO dates and formats, the ADO.Net runtime will deal with this for us.

Parameterizing queries offers other benefits, like SQL Injection prevention through input sanitization and other processing optimizations

Lets change the query to use parameters:

string sqlquery = @"SELECT UserID, Name, Mobile, Age, Date 
                    FROM tbluser 
                    WHERE Date BETWEEN @fromDate AND @toDate";

Then you need to pass the dates to the SqlCommand as a parameter,

notice here that we are using the .ToDate() method on the DateTime object to trim the time portion that the user might have provided.

SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.Add("@fromDate ", SqlDbType.Date);
sqlcomm.Parameters["@fromDate "].Value = date.Date;
sqlcomm.Parameters.Add("@toDate ", SqlDbType.Date);
sqlcomm.Parameters["@toDate "].Value = date.AddDays(1).Date;

This is a good post for comparing string concatenated SQL with parameterized queries: C#: SQL Injection Protection Using Parameterized Queries

UPDATE See this fiddle for a live demo: https://dotnetfiddle.net/8zLLrR

Because your controller is pre-sanitizing the input and constraining it to a DateTime typed value, SQL Injection is not likely to be an issue in this specific example, however the string-concatenation approach to build your query is generally a red flag in Web API as this is where your code is most vulnerable to external inputs.

SQL Server has an optimization where it tries to re-use execution plans when queries are re-executed. You will notice in SSMS when you run a query the first time, it is almost always slower than the second time if you re-execute the same query. That is because it has an internal table of stored execution plans and the SQL that you submit is the main key to this index.

When the date filter changes, if you use string concatenation this results in a different SQL so it is harder for the database engine to lookup an existing query execution plan, and in many cases it will generate a new one. When we use parameters, the actual SQL query is the same each time, so the optimizer can simply lookup any previously saved execution plans.

Later versions of SQL Server have additional optimisations to automatically parameterise query expressions for the execution plan index, but it's not 100% and not an excuse to be lazy developers ;)

You should not add a string like this

SELECT UserID, Name, Mobile, Age, Date From tbluser where Date="+date+"

This may cause SQL injection. Instead, follow this documentation using SQL parameters with type Date https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-6.0

Here is the example:

        string sqlquery = "SELECT UserID, Name, Mobile, Age, Date From tbluser where Date= @YourDate";
        SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
        sqlcomm.Parameters.Add("@YourDate", SqlDbType.Date);
        sqlcomm.Parameters["@YourDate"].Value = date;