asked
sql date range select query
hello all, i have two tables as shown below. User (uid,name,address) Userposition (id,uid,fromdate,todate) User (1, 'ABC' , 'Add') UserPosition (1,1,'01/01/2009','12/31/2009') (2,1,'01/01/2010','12/31/2010') (3,1,'01/01/2011',NULL) -- Last position have NULL value I need select query in which i will pass @startdate and @enddate and it will return record from UserPosition which are in passed range(i.e. between start date and end date). Thanks, Ankur
It's worth defining that more clearly: * Do you want to return ranges that are entirely contained within @startdate or @enddate? * Do you want to return ranges that only start between @startdate and @enddate? * For ranges that have no end date, would they appear whenever @enddate was greater than the defined start date?
01/12/2008 to 10/10/2010 will show (1,1,'01/01/2009','12/31/2009') (2,1,'01/01/2010','12/31/2010') ------------------------------------------------------------ 10/10/2010 to 10/10/2011 will show (2,1,'01/01/2010','12/31/2010') (3,1,'01/01/2011',NULL) ------------------------------------------------------------------- 01/12/2008 to 10/10/2011 will show (1,1,'01/01/2009','12/31/2009') (2,1,'01/01/2010','12/31/2010') (3,1,'01/01/2011',NULL)
Well how about this? DECLARE @startdate datetime, @enddate datetime SELECT @startdate = '2008-12-01', @enddate = '2011-10-10' DECLARE @Userposition AS TABLE (id int, uid int, fromdate datetime, todate datetime) INSERT INTO @UserPosition SELECT 1, 1, '01/01/2009', '12/31/2009' UNION ALL SELECT 2, 1, '01/01/2010', '12/31/2010' UNION ALL SELECT 3, 1, '01/01/2011', NULL ; SELECT * FROM @UserPosition WHERE fromdate BETWEEN @startdate AND @enddate OR todate BETWEEN @startdate AND @enddate
Solution : DECLARE @MaxDate DATETIME SET @MaxDate = '12/31/9999' SELECT * FROM tblUserPosition up WHERE ( (up.dtsStartDate between @dtStartDate And @dtEndDate) OR (isnull( up.dtsEndDate,@MaxDate) between @dtStartDate And @dtEndDate) OR (@dtStartDate between up.dtsStartDate And isnull( up.dtsEndDate,@MaxDate)) )
