t-sql datetime question

@EndDate = 2010-05-31

select * from TableName where OrderNo = 'AB103' and month(SendDate) = month(dateadd(mm,-1,@EndDate))

is returning 2 values which has same month (which is April) but different years like 2010 and 2009.

How can I change this query so that it returns value only of year 2010 and month remains April?

I have used (CONVERT(VARCHAR(6),(dateadd(mm,-1,2010-05-31)),112)+'01') instead for 2010-04-01 What is wrong with this code? SendDate = (CONVERT(VARCHAR(6),(dateadd(mm,-1,2010-05-31)),112)+'01')

I dont need value in 2010-04-01 fo开发者_如何学运维rm I want it in this form (CONVERT(VARCHAR(6),(dateadd(mm,-1, @EndDate)),112)+'01') What mistake I am making?

Anyone??

Use an explicit range check:

select *
  from TableName
  where OrderNo = 'AB103' 
   and SendDate >= '20100401' 
   and SendDate < '20100501';

This is correct and also efficient because this expression is SARGable and can leverage an index on the SendDate column. Using explicit comparison operators avoid the ambiguity of between operator (does it include the right side or not? I can never remember, so avoid it altogether). Also using the canonical date format 'yyyymmdd' avoids any issue from running your code on different locales (SET DATEFORMAT).

try:

      select * from TableName 
      where OrderNo = 'AB103' 
          and DateDiff(month, SendDate, '2010-04-01') = 0 

You can use "between" to limit the range of dates:

select *
  from TableName
  where OrderNo = 'AB103' and SendDate between '4/1/2010' and '4/30/2011'

Note that this assumes you are storing dates without times. It would not return a SendDate that was during the afternoon of 4/30/11.

I would do it this way

select *
  from TableName
  where OrderNo = 'AB103' 
    and DatePart(yyyy,SendDate) = 2010 
    and DatePart(m,SendDate) = 4