getting month from a table according to the selected month

Question

I am trying to get a report of patients department and month wise. I am selecting a department and the the month. How can I get only the selected months records department wise. I am trying the following query but not working :

SELECT MONTH(select convert(varchar,creation_Date,105) from Patient_Ref_master)

Note that [the accepted answer](http://stackoverflow.com/a/17887727/61305) is grossly inefficient if you currently (or ever will) have an index on the `creation_Date` column. It also won't work the way you think it works once you have more than a year of data. An open-ended range, [as in Habo's answer](http://stackoverflow.com/a/17888631/61305), is going to work much better (even if the accepted answer adds a `YEAR()` filter). — Aaron Bertrand, Jul 26 '13 at 19:03

HABO · Answer 1 · 2013-07-27T15:54:34.440

If you want a single month/year pair the following query will work:

select *
  from Patient_Ref_Master
  where Cast( '20130801' as Date ) <= Creation_Date and Creation_Date < Cast( '20130901' as Date )

It has the advantage that the query can use an index since it does not need to perform a calculation on each row.

It is often helpful to calculate the limits prior to the query, e.g. the current month:

declare @Start as Date = DateAdd( month, DateDiff( month, 0, GetDate() ), 0 );
declare @End as Date = DateAdd( month, 1, @Start );
select *
  from Patient_Ref_Master
  where @Start <= Creation_Date and Creation_Date < @End

EDIT: If the use of comparison operators thrown together willy-nilly with boolean operators is overwhelming, I offer the following simplification:

declare @Patient_Ref_Master as Table ( Id Int Identity, Creation_Date Date );
insert into @Patient_Ref_Master ( Creation_Date ) values
  ( '20130731' ), ( '20130801' ), ( '20130815' ), ( '20130831' ), ( '20130901' );
select * from @Patient_Ref_Master;

declare @Start as Date = DateAdd( month, DateDiff( month, 0, Cast( '20130829' as Date ) ), 0 );
declare @End as Date = DateAdd( month, 1, @Start );

-- Incomprehensible   WHERE   clause:
select *
  from @Patient_Ref_Master
  where @Start <= Creation_Date and Creation_Date < @End;

-- Simplified AB version:
with
  JustRight as (
    select *
      from @Patient_Ref_Master
      where Creation_Date in ( @Start ) ),
  NotTooLate as (
    select *
      from @Patient_Ref_Master
      where Sign( DateDiff( day, @End, Creation_Date ) ) in ( -1 ) ),
  NotTooSoon as (
    select *
      from @Patient_Ref_Master
      -- NB: Do NOT include zero in the set of matches. That would be too easy.
      where Sign( DateDiff( day, Creation_Date, @Start ) ) in ( -1 ) ),
  TheResult as (
    select *
      from JustRight
    union
    select *
      from NotTooLate
    intersect
    select *
      from NotTooSoon )
  select * from TheResult;

No, IN is not listed in the documentation as a comparison operator.

+1 but this is such a weird way to structure your clause - variable <= column and column < variable. Isn't column >= variable and column < variable much more logical? I initially thought you had the operator inverted. — Aaron Bertrand, Jul 26 '13 at 18:45
@AaronBertrand - Someone once had a fit when they saw `BETWEEN` used with, of all things, some sort of date/time data. I find this is a reasonably clear way to write "between" related logic, particularly when dealing with mixed open/closed intervals. Frankly, I can't tell a right side up `<` from one that is inverted. — HABO, Jul 26 '13 at 19:09
(1) I wasn't proposing `BETWEEN` at all - [I hate `BETWEEN` for date range queries](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx). But usually we have the column on the left-hand side and the comparator on the right, e.g. `Creation_Date >= @Start AND Creation_Date < @End`. I don't see the form you've used very often (and don't find it very logical), so I asked. (2) I think you know what I meant by inverted. — Aaron Bertrand, Jul 26 '13 at 19:12

score -2 · Accepted Answer · edited May 23 '17 at 12:05

-2

select * from Patient_Ref_master
where MONTH(creation_Date)=1 --target month here, JAN as example

SQLFIDDLE
Also I found similar question where you can get extra workaround as well as other answer in this thread.

edited May 23 '17 at 12:05

Community

1
1

answered Jul 26 '13 at 17:44

revoua

2,044
1
21
28

2

Applying MONTH() to the column means that an index seek will never be used. Also it assumes that we want January 2000, January 2001, January 2002, etc. as opposed to January of a specific year. – Aaron Bertrand Jul 26 '13 at 18:47

getting month from a table according to the selected month

2 Answers2