query to return a date range.

I would like to use the query below to return a date range but its key is the LOUReturn field which determins which records in the date field. i'd also like to drop the hour/min/sec from the query. the code below parses ok but doesn't return any thing, but is i run it without the between date part all the data comes back. also I'll be building this query into SSRS report for weekly run.

use PCTSRS
select d.CM_ITEM_ID, d.CM_ITEM_NAME, d.CLIN, d.SEARCH_APPROVING_OFFICIAL as 'SAO', d.SEARCH_PROFESSIONAL as 'SP',
         r.FixedByPTO, r.DateReturned, r.LOUReturn, r.ErrorText
      from CLG_RETURNS r
            inner join CLG_DOCUMENTS d on r.ItemID = d.CM_ITEM_ID
      where r.LOUReturn = (1)and r.DateReturned between '2011-02-16 00:00:00.000' and '2010-03-10 00:00:00.000'
      
      order by r.DateReturned desc
richa1960Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ThomasianConnect With a Mentor Commented:
>> r.DateReturned between '2011-02-16 00:00:00.000' and '2010-03-10 00:00:00.000'

That is because your "from" date is greater than you "to" date

Maybe you meant:

r.DateReturned between '2011-02-16 00:00:00.000' and '2011-03-10 00:00:00.000'
0
 
reb73Commented:
Swap the datereturned values around, the lower value first and the higher value after..
0
 
itcoupleCommented:
Hi

Also make sure your date field doesn't contain time if it does than use DateDiff function for compare dates whcih usually is recommended for any date comparisons criterias.

Regards
Emil
0
 
reb73Commented:
You could use a template like below (or even more preferable convert it to a parameterized stored procedure) -
use PCTSRS

declare @startdate datetime, @enddate datetime, @LOUReturn int

select	 @startdate = '2011-02-16'	-- Populate startdate her
	,@enddate = '2011-03-01'	-- populate enddate here
	,@LOUReturn = 1			-- Populate LouReturn value here

set @enddate = dateadd(second, -1, dateadd(day,1,@enddate))

select d.CM_ITEM_ID, d.CM_ITEM_NAME, d.CLIN, d.SEARCH_APPROVING_OFFICIAL as 'SAO', d.SEARCH_PROFESSIONAL as 'SP',
         r.FixedByPTO, r.DateReturned, r.LOUReturn, r.ErrorText 
from CLG_RETURNS r
            inner join CLG_DOCUMENTS d on r.ItemID = d.CM_ITEM_ID
where r.LOUReturn = @LOUReturn and r.DateReturned between @startdate and @enddate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.