Date Validation - - 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 2000 Forums

Transact-SQL (2000)

Date Validation -

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-12 : 03:37:30

Dear All,
I have the following scenario..,

I have an input as date. If the user can enter any date in that input box. now i just need to validate the date like following

For future date
the input date should not exceed the first day of the next month
Eg:
Current date 12/12/2006
input date : 12/12/2008
the o/p Should be 01/01/2007

for Past date
The input sholud not exceed before two months otherwise 60 days
eg:
Current date : 12/12/2006
input date : 01/01/2006
the o/p should be 12/10/2006 (ie) 60 days difference

Note: I want to validate through sql only . No need of Front end..!

can any one able to help me please. Urgent situation

Thanks
Krishna
Krishnakumar.C

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 03:43:10

Future date
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

Past date
SELECT DATEADD(day, DATEDIFF(day, 61, GETDATE()), 0)

Peter Larsson
Helsingborg, Sweden

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-12 : 04:18:05

thanks peter

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-12 : 04:44:33

But for this scenario it fails..,
Declare @dt Datetime
Select @Dt = '2007/11/01'

if month(@dt) = month(Getdate())
Begin
Select @dt = @dt
End
Else if month(@dt) > Month(Getdate())
Begin
SELECT DATEADD(month, DATEDIFF(month, 0, getdate()), 31)
End
Else if month(@dt) < Month(Getdate())
Begin
SELECT DATEADD(day, DATEDIFF(day, 61, getdate()), 0)
End

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-12-12 : 04:45:25

for the above scenario also i have to set the date as 01/01/2007

Thanks
Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 05:34:16

Write a proper algorithm/logic will help. You only compare month, but not year.

Try this

DECLARE	@dt DATETIME

SELECT	@dt = '20071101'

IF @dt > DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)
	SET @dt = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 31)

IF @dt < DATEADD(day, DATEDIFF(day, 61, GETDATE()), 0)
	SET @dt = DATEADD(day, DATEDIFF(day, 61, GETDATE()), 0)

SELECT	@dt

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources