Nested if problem - SQL Server Forums

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)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources