SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Nested if problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scottichrosaviakosmos
Yak Posting Veteran

Russia
66 Posts

Posted - 07/02/2010 :  12:04:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 07/02/2010 :  13:32:16  Show Profile  Reply with Quote
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

Russia
66 Posts

Posted - 07/03/2010 :  01:56:12  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000