July 2, 2010 at 10:06 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 .
can anyone help. and send me corrent method.
the SQL sode is below.:
alter proc datedifference
@enterdate datetime
as
declare @currentdate datetime
set @currentdate=getdate()
--declare @enterdate datetime
--set @enterdate='02/09/2007'
declare @currentyear int
declare @enteryear int
declare @subscurrentyear int
declare @result int
set @result=0
declare @currentday int
set @currentday=day(@currentdate)
declare @currentmonth int
set @currentmonth=month(@currentdate)
declare @entermonth int
set @entermonth=month(@enterdate)
declare @enterday int
set @enterday=day(@enterdate)
set @currentyear=year(@currentdate)
set @subscurrentyear=@currentyear-3
set @enteryear=year(@enterdate)
if(@enteryear<=@currentyear and @enteryear>=@subscurrentyear)
begin
if( @entermonth<=@currentmonth)
begin
if(@enterday<@currentday)
begin
print 'valid date'
end
end
end
else
print 'invalid date'
July 2, 2010 at 10:18 am
If all you are doing is a check to see if the entered date is not greater than today and not more than three years ago then your code seems a little complex. the below would take core of that check.
alter proc datedifference
@enterdate datetime
as
declare @currentdate datetime
If @enterdate>getdate()
goto EndOfProc
If @enterdate<dateadd(yy,-3,getdate())
goto EndofProc
print 'valid date'
EndOfProc:
print 'invalid date'
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
July 2, 2010 at 11:18 am
The problem with how you're doing this is that you have 3 nested IF's, but only the outer IF has an associated ELSE. So if your condition evaluates true for the outer IF, but then fails for either of the two inner IF's, you will get nothing returned because there is no ELSE statements defined for those.
Consider the following ...
declare @string varchar(20) = 'empty'
if (@enteryear <= @currentyear and @enteryear >= @subscurrentyear)
begin
if (@entermonth <= @currentmonth)
begin
if (@enterday < @currentday)
begin
select @string = 'valid date'
end
else
begin
select @string = '3rd IF false'
end
end
else
begin
select @string = '2nd IF false'
end
end
else
begin
select @string = '1st IF false'
end
select @string
However, if you really only want a 'valid date' response if all 3 IF's evaluate to true, you can simplify to this:
if (@enteryear <= @currentyear and @enteryear >= @subscurrentyear and @entermonth <= @currentmonth and @enterday < @currentday)
select 'valid date'
else
select 'invalid date'
*edit: formatting
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply