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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Nested if problem

Author  Topic 

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-02 : 12:04:27
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'


scoo

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-02 : 13:32:16
Something like this?
DECLARE @EnterDate DATETIME
DECLARE @CurrentDate DATETIME

--SET @EnterDate = '20070902'
SET @EnterDate = '20070601'

SET @CurrentDate = GETDATE()

SELECT
CASE
WHEN @EnterDate BETWEEN DATEADD(YEAR, -3, @CurrentDate) AND @CurrentDate
THEN 'Valid'
ELSE 'Not Valid'
END
If you want to strip off the time portion of the DATETIME you can do soemthing like:
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
Go to Top of Page

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-07-03 : 01:56:12
yes ur answer is very close but i want that the start date can be anything and end date also can be anything(not mandatory to be currentdate). now i want the start date not to be greater or less then end date not even one day greater or less.

scoo
Go to Top of Page
   

- Advertisement -