SQL query not returning all rows

Question

I have a table in SQL Server which has many rows, with a created_date column. This column has rows starting from the year 2006.

I want to get all the rows which were created in and before February, 2015. This stored procedure has a parameter @month. It should select all the rows based on the @month value entered.

Here is my query:

select * 
from products 
where 1=1 
  and year(created_date) <= 2015 
  and month(created_date) <= @month 

But this query returns only the records which were created in and before February month of previous years excluding records which were created in other months of 2014 (e.g., 2014-03-17, 2014-05-05 are excluded).

I have to get a new date based on the @month entered. Suppose I entered month July, I want to have condition "where created_date < 2015-07-31". So I can do something like this,

So I have changed my query,

declare @date datetime
set @date = CAST((2015 + '-' + @month + '-' + 28) as datetime)
select * 
from products 
where 1=1 
and year(created_date) <= 2015 

But this query returns 1905-08-08 00:00:00.000 and I want to get 2015-02-28 00:00:00.000 and also I have to find total number of days based on the @month entered so that I can pass that number to CAST((2015 + '-' + @month + '-' + 28) as datetime) instead of 28.

Answer 1

Just use a single date and specify that the created_date column must be less than that date:

declare @newestDate datetime = '2015-03-01'

select * 
from products
where created_date < @newestDate

Note that I set the date to be the 1st March but in the query I use < rather than <=. This will cope with a created_date value including a time component, e.g. 2015-02-28 23:59:59

To generate the value of "February of previous year", you may actually be wanting to use the current month of last year, if so, your date would be:

declare @newestDate datetime = 
    DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0))

This would then work next month (i.e. March) and would give your query a rolling month.

Answer 2

Always compare date/time fields to a single value where possible -- this is best for performance as well. You can "round" dates with DATEADD and DATEDIFF.

DECLARE @startOfNextMonth DATETIME;
SELECT @startOfNextMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0);
select * from products where 1=1 and created_date < @startOfNextMonth;

String manipulation to convert dates is also possible, but tends to perform worse and is tricky to get right. This technique applies in general if you want to "round" to years, minutes, 15-second periods, etcetera, which is much harder with strings.

If you can, rewrite your stored procedure to not take a @month parameter but an absolute value that your clients calculate -- it's more general and tends to be easier to work with. Your query then simply reduces to

select * from products where 1=1 and created_date < @limit;

Of course, if you must use a @month, you can construct this offset in the stored procedure itself:

DECLARE @limit DATETIME = 
    DATEFROMPARTS(DATEPART(YEAR, GETDATE()), @month, 1)
;

This takes advantage of DATEFROMPARTS, which was introduced with SQL Server 2012. For previous versions, reliably constructing a date is considerably messier. There are many wrong ways to do it that will break if the regional settings are set to something unexpected. DATEADD is again of assistance:

DECLARE @limit DATETIME = 
    DATEADD(MONTH, (DATEPART(YEAR, GETDATE()) - 1900) * 12 + @month - 1, 0)
;

These are not the only methods to construct datetime values, but string manipulation is in any case tricky (because the only reliable format that will not break under regional settings is YYYYMMDD, no dashes).

Answer 3

In this question: Create a date with T-SQL you will see how to construct an sql-server date data type given a certain year and month. Suppose you call that 'my_date'.

You will then be able to do the following:

SELECT * FROM products WHERE created_date < my_date