Interpolate a variable date in WHERE statement of SQL Query string

Question

I'm developing an API using .NET Web API2. One of the endpoints receive a date as an argument and it needs to return a list of items depending on that date. The problem is that I'm not being able to interpolate this date inside of the SQL Query.

Lets say that I need this query as a result, whcih it works if I hardcode the date

Select platform_code, platform_state, COUNT(*) AS 'total cycle', MIN(events.created_at) AS 'first reading'
from events LEFT JOIN platforms ON platforms.platform_code = events.platform_code
WHERE events.created_at BETWEEN '22/06/2020 0:00:00' AND getdate() 
group by platform_code, platform_state

And this is my controller code:

{
    [RoutePrefix("api/queries")]
    public class QueriesController : ApiController
    {
        [Route("dashboard")]
        public HttpResponseMessage GetDashboard(string start_date = null)
        {
           DateTime parsed_start_date ;

            using (var ctx = new RFID_TESTEntities())
            {
                if (start_date != null)
                {
                     parsed_start_date = DateTime.ParseExact(start_date, "dd-MM-yyyy", new CultureInfo("es-ES"));

                }
                else
                {
                    parsed_start_date = DateTime.ParseExact("01-01-2020", "dd-MM-yyyy", new CultureInfo("es-ES"));
                }

                String query = "Select platform_code, platform_state, COUNT(*) AS 'total cycle', MIN(events.created_at) AS 'first reading'
                from events LEFT JOIN platforms ON platforms.platform_code = events.platform_code
                WHERE events.created_at BETWEEN " + parsed_start_date + "  AND getdate() 
                group by platform_code, platform_state";
                var platforms = ctx
                    .Database.SqlQuery<Dashboard>(query)
                    .ToList();

If I try to run this, the parsed_start_date variable in the where clause lacks the simple quote, therefore I got a SQL syntax error:

"Select platform_code, platform_state, COUNT(*) AS 'total cycle', MIN(events.created_at) AS 'first reading' from events LEFT JOIN platforms ON platforms.platform_code = events.platform_code
WHERE events.created_at BETWEEN 01/01/2020 0:00:00 AND getdate() 
group by platform_code, platform_state"

I have tried to cast the variable into a string before using it in the query but apparently it behaves the same way.

Use a parameter instead of building a sql string. It will enable you to pass it as an actual Date and you will avoid syntax errors. — Crowcoder, Jun 30 '20 at 12:49
You should use a parameter indeed. What makes an error in your string is the lack of quote : BETWEEN '" + parsed_start_date + "'. If you look closely it is : BETWEEN ' " + ... + " ' (single quote before and after the double quotes). When writing such a long string I would use the @"..." string which gives you the possibility to write on more than one line. — Kirjava, Jun 30 '20 at 12:51
At first I was using LINQ Lambda, but the real query is a little bit more complicated that I show in the OP (I simplified for clarity sake) and I wasnt able to translate the SQL into LINQ, so I decided to try this way. — Ernesto G, Jun 30 '20 at 12:51
I guess you mean passing a parameter like this: https://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it, I will check it out. — Ernesto G, Jun 30 '20 at 12:55

score 1 · Accepted Answer · answered Jun 30 '20 at 13:47

Just in case someone finds it useful, this is finally working for me using sql parameters which as the comments said is a lot better in terms of safety than what I was trying to do first:

 String query = "Select platform_code, platform_state, COUNT(*) AS 'total cycle', MIN(events.created_at) AS 'first reading'
                from events LEFT JOIN platforms ON platforms.platform_code = events.platform_code
                WHERE events.created_at BETWEEN @start_date  AND getdate() 
                group by platform_code, platform_state";
                var platforms = ctx
                    .Database.SqlQuery<Dashboard>(query, , new SqlParameter("@start_date", parsed_start_date))
                    .ToList();

Well done. I can't tell you how often people ignore the advice to use parameters. — Crowcoder, Jun 30 '20 at 13:59
this is the best way. For a better visibility, use the @ before your string and write it on multiple lines as you would do into an SQL editor. It will be much simple to read. — Kirjava, Jun 30 '20 at 14:14

score -1 · Answer 2 · answered Jun 30 '20 at 12:59

-1

You could string interpolate the date and add the single quotes there

$"'{parsed_start_date}'"

answered Jun 30 '20 at 12:59

RWael

19
3

1

Parameters are the only answer, this also leads to SQL injections. – CShark Jun 30 '20 at 13:00

Interpolate a variable date in WHERE statement of SQL Query string

2 Answers2