July 3, 2010 at 5:24 am
i have a procedure and i want to check whether the date entered by user should not be greater then current day and not less then 3 years
so i am checking year month and days by matching with IF statement.
now when i m trying to run then i m getting error of nesting of if .
Important thing is that i also have to check leap year. anf if leap year then check the condition according to that . but i m getting error because of if satatement.
can anyone help. and send me corrent method.
the SQL sode is below.:
create proc finddatediffnew
@enddate DATETIME,
@Startdate DATETIME
as
--DECLARE @EnterDate DATETIME
--DECLARE @CurrentDate DATETIME
declare @Eyear int
set @Eyear=year(@enddate )
declare @Cyear int
set @cyear=year(@Startdate)
--SET @EnterDate = '20070902'
--SET @EnterDate = '2008/7/8'
--SET @CurrentDate = GETDATE()
if((@Eyear/4)=0 and (@Cyear/4)=0)
SELECT
CASE
WHEN @enddate BETWEEN DATEADD(YEAR, -3, @Startdate) AND @Startdate
THEN '0'--'valid'
ELSE '1'--'not valid'
else
SELECT
CASE
WHEN @enddate BETWEEN DATEADD(YEAR, -3, @Startdate) AND @Startdate
THEN '0'--'valid'
ELSE '1'--'not valid'
July 3, 2010 at 6:20 am
Your CASE statements need to be terminated with an END
SELECT CASE
WHEN @enddate BETWEEN DATEADD(YEAR, -3, @Startdate) AND @Startdate THEN '0'--'valid'
ELSE '1'--'not valid'
END AS ValidDates
I'm not sure why you've bothered with the IF. Both branches of the IF are the same. You said you need to check leap year, but why? What is different if one (or both) dates are leap years? SQL's date functions work fine with leap years
btw, your check for leap years is invalid. Your IF will identify 1900 and 2100 as leap years. They are not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply