By acquiring a way to use BETWEEN with a table, since it will work, but performance will be worse in each case:
- It at best consumes more CPU in order to do some calculations on strings, and not work with them as dates.
- With the worst power, the table scans every row in the table, but if your columns have indexes, then you can search with the right query. This could be a HUGE performance difference, since enforcing constraints on BETWEEN clauses will be disabled using the index.
I suggest the following if you have a pointer to date columns and do not care about performance at all:
DECLARE @FromDate date = '20111101', @ToDate date = '20120201'; SELECT * FROM dbo.YourTable T WHERE ( T.[Year] > Year(@FromDate) OR ( T.[Year] = Year(@FromDate) AND T.[Month] >= Month(@FromDate) ) ) AND ( T.[Year] < Year(@ToDate) OR ( T.[Year] = Year(@ToDate) AND T.[Month] <= Month(@ToDate) ) );
However, it is clear that you do not want to use such a construction, since it is very inconvenient. So, here is a trade-off request that at least uses numerical calculations and will use less CPU than calculating a date to string conversion (although this is not enough to compensate for forced scanning, which is a real performance issue).
SELECT * FROM dbo.YourTable T WHERE T.[Year] * 100 + T.[Month] BETWEEN 201111 AND 201202;
If you have an index on Year , you can get a big boost by sending a request as follows, which has the ability to search:
SELECT * FROM dbo.YourTable T WHERE T.[Year] * 100 + T.[Month] BETWEEN 201111 AND 201202 AND T.[Year] BETWEEN 2011 AND 2012;
While this violates your requirement to use a single BETWEEN expression, it is not too painful and will work very well with the Year index.
You can also change the table. Honestly, using separate numbers for your date details instead of a single column with a date data type is not good. The reason is that this is not good, because of the exact problem you are facing right now, it is very difficult to request.
In some data storage scenarios where many save bytes are important, I could imagine situations where you can save the date as a number (e.g. 201111 ), but this is not recommended. The best solution is to change the table to use dates instead of separating the numeric values of the month and year. Just save the first day of the month by finding out that it costs for the whole month.
If changing the way you use these columns is not an option, but you can still change your table, then you can add a constant computed column:
ALTER Table dbo.YourTable ADD ActualDate AS (DateAdd(year, [Year] - 1900, DateAdd(month, [Month], '18991201'))) PERSISTED;
With this, you can simply do:
SELECT * FROM dbo.YourTable WHERE ActualDate BETWEEN '20111101' AND '20120201';
The keyword PERSISTED means that as long as you get the scan anyway, it does not need to do any calculations on each line, because the expression is evaluated for each INSERT or UPDATE and stored in the line. But you can get a request if you add an index to this column, which will make it work very well (although in general it is still not as ideal as switching to using the actual date column, as this will require more space and affect INSERT and UPDATE):
CREATE NONCLUSTERED INDEX IX_YourTable_ActualDate ON dbo.YourTable (ActualDate);
Summary: if you really cannot change the table in any way, then you will have to compromise somehow. It is not possible to get the simple syntax you want, which will also work well when your dates are stored as separate columns.