Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Onamuji
Aged Yak Warrior
504 Posts |
 Posted - 2002-08-27 : 12:17:16
|
Ok so I have this report I need to run where the user can select ('=', '!=', '>', '>=', '<', '<=') do compare a date field in a table to the date they specify. So I wrote this simple function to return 1 if the comparison suceeded or 0 if it was invalid or not true...CREATE FUNCTION CompareDates (@compareType VARCHAR(2), @date1 DATETIME, @date2 DATETIME) RETURNS BIT AS BEGIN DECLARE @retCode BIT IF @compareType NOT IN ('=', '!=', '<', '<=', '>', '>=') OR @compareType IS NULL OR @date1 IS NULL OR @date2 IS NULL SET @retCode = 0 ELSE IF @compareType = '=' SET @retCode = CASE WHEN @date1 = @date2 THEN 1 ELSE 0 END ELSE IF @compareType = '!=' SET @retCode = CASE WHEN @date1 <> @date2 THEN 1 ELSE 0 END ELSE IF @compareType = '<' SET @retCode = CASE WHEN @date1 < @date2 THEN 1 ELSE 0 END ELSE IF @compareType = '<=' SET @retCode = CASE WHEN @date1 <= @date2 THEN 1 ELSE 0 END ELSE IF @compareType = '>' SET @retCode = CASE WHEN @date1 > @date2 THEN 1 ELSE 0 END ELSE IF @compareType = '>=' SET @retCode = CASE WHEN @date1 >= @date2 THEN 1 ELSE 0 END ELSE SET @retCode = 0 RETURN @retCode END(by the way if any of you have something better i'd like to see  )So then I have four fields I need to compare this too... they can be null but the function should return 0 if they are .... but we will want to ignore the ones that return 0 because the values are null... so i have in the where clause something like this:(1 = dbo.CompareDates(@TargetStartDateCompare, TargetStartDate, @TargetStartDate) OR (@TargetStartCompare IS NULL OR @TargetStartDate IS NULL)) AND (1 = dbo.CompareDates(@TargetEndDateCompare, TargetEndDate, @TargetEndDate) OR (@TargetEndDateCompare IS NULL OR @TargetEndDate IS NULL))Now lets say @TargetStartDateCompare = '>=' @TargetStartDate = '08/01/2002' @TargetEndDateCompare = '<=' @TargetEndDate = '09/030/2003'I have verified that these values exist... however my logic must be messed up because I don't get any results :( can anyone see something wrong with my logic? if not i'll post the ENTIRE thing with sample data and everything ... |
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-08-27 : 12:45:13
|
| never mind it was a bad () set in the query ... one too many :) thanks anyway :) |
 |
|
|
|
|
|
|
|
Dynamic Date Comparison