Not getting back all data

Last Post 14 Feb 2006 09:35 AM by nosepicker. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
werice
New Member
New Member

--
14 Feb 2006 09:05 AM
I have the following query


declare @FromMonth int, @FromYear int, @ToMonth int, @ToYear int
set @FromMonth = 06
set @FromYear = 2004
set @ToMonth = 06
set @ToYear = 2005


select
contracteffectivedate
from
acedb_first_prod..ct_contract
where
(year(contracteffectivedate) >= @FromYear
and month(contracteffectivedate) >= @FromMonth)
and
(year(contracteffectivedate) <= @ToYear
and month(contracteffectivedate) <= @ToMonth )
order by
contracteffectivedate

As you can see I want all dates between month = 06 year = 2004

and month = 06 and year = 2005.

The thing is, this query returns only month = 06 year = 2004 month = 02 year = 2005. All month between 07, 08, 09, 10, 11, 12, 01, 02, 03, 04 and 05 are not returning (I figure they should). Does anyone know what the problem is with the query?

nosepicker
New Member
New Member

--
14 Feb 2006 09:35 AM
You query won't work mainly because of your month conditions. All of the parentheses you are using don't really matter because all of the 'AND' conditions have be true for a record to be selected. If you think about it, with the conditions you established, the only month that will satisfy all of the conditions is '06', because only '06' will be both >= @FromMonth ('06') and also <= @ToMonth ('06').

The easiest way to select all records from June 2004 through June 2005 is like this:

WHERE contracteffectivedate >= CONVERT(datetime, '2004-06-01')
AND contracteffectivedate < CONVERT(datetime, '2005-07-01')

Notice that the second condition is a "less than" instead of "less than or equal to". This should be faster than your original method because this doesn't apply a function like YEAR or MONTH to the columns, which is a slower method.

If you must use the parameters because they are entered or passed in from an app or something, then you can perhaps do something like this (after you change the datatypes to varchar):

DECLARE @FromMonth varchar(2), @FromYear varchar(4), @ToMonth varchar(2), @ToYear varchar(4)

WHERE contracteffectivedate >= CONVERT(datetime, @FromYear + @FromMonth + '01')
AND contracteffectivedate < DATEADD(mm, 1, CONVERT(datetime @ToYear + @ToMonth + '01'))

You are not authorized to post a reply.

Acceptable Use Policy