Query a database table and compare DateTime values

Today I had a problem that with the help of my fellow developers at Spindogs, we found a solution, but I was wondering, is there another way of doing it? 

The issue is that I have a column in a database table that has a DateTime value e.g. 2023-02-09 and I needed to query that table to find all dates by month, so the value 02. 

I started with the following code: 

 

  var sql = scope.SqlContext.Sql().SelectAll()
          .From<APIModel>()
          .Where<APIModel>(o => o.Time.Month == DateTime.Now.Month);

 

But this gave the error :

System.InvalidOperationException: 'variable 'o' of type 'Our.Site.Core.API.Models.APIModel' referenced from scope '', but it is not defined'

 

The next attempt looked like this: 

  var month = DateTime.Now.Month.ToString();

  var sql = scope.SqlContext.Sql().SelectAll()
          .From<TidesAPIModel>()
          .Where<TidesAPIModel>(o => o.Time.Month == month);

This was in the hope that comparing the database value to a string of month would fix things, it didn't. I got the same error as above.

 

Next attempt was similar to the above code but we changed the .SelectAll() to .Select(*)

We also changed the '==' to '<' and to my surprise, it worked. Well, it sort of worked, I was now pulling back all the dates that were less than the current month. Not the exact results I wanted.

Then a suggestion was to just get the query working in Sql Server Management Studio - the SQL statement looked like this :

SELECT TOP (1000) [Id]
      ,[Time]
      ,[FreeFlowOpenTime]
      ,[FreeFlowCloseTime]
      ,[Day_Id]
      ,[CreatedOn]
      ,[UpdatedOn]
      ,[Height]
  FROM [UMBRACO_DATABASE].[dbo].[TideTimes]
  WHERE DATEPART(month, [Time]) = '2'

 

This brought back the results I was hoping for, all the data that had a month of 2 (February).

What we then tried back in code was to replicate this and we came up with the following :

 

  var sql = scope.SqlContext.Sql().Select("*")
            .From<TidesAPIModel>()
            .Where("DATEPART(month, [Time]) = '"+ month +"'");

 

The value for 'month' gets passed so that I can query different months but Eureka! It worked. 

So, it seems that the '==' was the issue. I need to read up about why it's an issue but if you've had this problem and used a different solution, I'd be really interested to hear from you.

UPDATE: 
There is a safer way to do this with the use of parameters. The previous way was open to SQL Injection due to string concatenation, the "+ month +" bit in my code above. 

The suggestion to use parameters came from Nik on Twitter after I tweeted that I'd written this blog. 

 

and the code now looks like this: 

 var sql = scope.SqlContext.Sql().Select("*")
           .From<TidesAPIModel>()
           .Where("DATEPART(month, [Time]) = @0", month);

Thanks James, Nigel and Dom for troubleshooting this with me today. #h5yr 

Banner image created with DALL-E 

Published on: 09 February 2023