How to check date lies in the month from OCT to March and jan to April

1

How to check date lies in the month from Oct to March and Jan to April,
i have to write a two sql query in

  1. date of joining between Oct to March of next year.
  2. date of joining between Jan to April of next year.
2 
-- Oct to March of next year
WHERE [date of joining] >= '20161001' AND [date of joining] < '20170401' -- NOTE: Endpoint is month-after-cutoff

-- Jan to April of next year
WHERE [date of joining] >= '20170101' AND [date of joining] < '20170501' -- NOTE: Endpoint is month-after-cutoff
3

I have to add one year from the date joining,
then perform a two query

  1. date of joining between Oct to March of that year.
  2. date of joining between Jan to April of that year.
4 
WHERE DATEADD(Year, 1, [date of joining]) ...

Beware of the outcome of adding one year to any date that happens to be 29th February ... or if what you actually want is "end of month" and you add one year to 28th February

5

Actually ... doesn't that mean that what you actually want is anyone who join in the LAST year between those dates, rather than anyone who, in a year's time, will have joined one year before?

There may be subtle differences in the outcome of the differences in logic ...

6

Try this one it may be helpful to you

SELECT  *
FROM your table
--this will transform your date in a varchar with 2 digits for month and 2 digits for day
WHERE  RIGHT('0' + RTRIM(CAST (MONTH(<yourdatefield>) as varchar(2))), 2) + 
       RIGHT('0' + RTRIM(cast(DAY(<yourdatefield>) as varchar(2))), 2)
BETWEEN '0310' and '0410'

Go this one it may be helpful to you. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9a792f82-162e-4623-9184-30ee170d7883/check-month-and-day-fields-lies-between-two-dates?forum=transactsql

7

Likely to have poor performance though, although if the requirement is to be able to report on "date parts" there isn't much alternative to using MONTH etc. Personally I wouldn't join them into a string before comparison, although a WHERE clause using just numerics is a bit cumbersome (in this use-case)

If there is a need to report on day/month of joining (for example) I think it would be better to "store" that in separate column(s), in addition to the full date, in the table (or as a Computed Column) so that it can be reported on more efficiently (e.g. by adding an index). Keeps all the "Calculate Month of Joining" logic in one place too :slight_smile:

8

declare @yr int=2017, @startmo tinyint=1, @endmo tinyint=3;
select *
from mytable
where year(DatetimeCol )=@yr
and month(DatetimeCol ) between @startmo and @endmo ;

9

note that that is not SARGable and will therefore if indexes are avilable will be much slower than just checking

WHERE DatetimeCol >= @StartDate AND DatetimeCol < @EndDatePlusOneDay
10

One could also create 2 computed columns with the persist option and index them.
ALTER TABLE MyTable ADD CmptdYear as Year(Mydate) Persisted, CmptdMonth as Month(MyDate) Persisted;
GO
CREATE INDEX MyTableYearMonth ON MyTable(CmptdYear , CmptdMonth);
GO

11

Yes, that would work well and be SARGable. Rhe Date Range test will be easier in the situation where the start and end points span a year end. If the O/P always wants Year and Month, and never wants them individually, then one option would be to have an Indexed, Computed, column as a composite value for Year & Month - e.g. (Year * 100) + Month. But then whenever a range test is needed it will be a bit counter-intuitive to use, as the Start & Endpoints will have to be presented in that calculated form.