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

General SQL Server Forums

New to SQL Server Programming

date validation

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-13 : 05:21:14

hi,
this is my table values:

select * from empmaster
emp.no fromdate todate
----------------------------------
E 3/13/2007 4/7/2007
E 4/13/2007 6/13/2007

i am going to pass some date for example 5/20/2007.

so i have to compare this 5/20/2007 which lies in between 3/13/2007 4/7/2007 or 4/13/2007 6/13/2007.if means i have to print not allowed other wise allowed. and the date should not be less(if 2/10/2007). like thi i have many from date and todate in my table. if i give 7/20/2007 means it should print allowed becos it doesnot falls in between any date o the emp.no=e. this should be depent on the emp.no also.

so please help me to do this.please show me query for this.or spc

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-13 : 06:15:27

please do any one reply me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-13 : 08:01:40

[code]
declare @empmaster table
(
empno char(1),
fromdate datetime,
todate datetime
)

insert into @empmaster
select 'E', '20070313', '20070407' union all
select 'E', '20070413', '20070613'

declare @somedate datetime

select @somedate = '20070520'

select *,
case when @somedate between fromdate and todate then 'Allow' else 'Not Allow' end
from @empmaster
[/code]

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-13 : 23:08:02

hi khtan, i used youe query

This is the output
empno fromdate todate status
---------------------------------------------------------------
E 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 Not Allow
E 2007-04-01 00:00:00.000 2007-07-01 00:00:00.000 Not Allow
E 2007-08-01 00:00:00.000 2008-08-01 00:00:00.000 Not Allow
E 2008-10-01 00:00:00.000 2008-12-01 00:00:00.000 Allow-->wrong

i given @somedate as 2008-12-01.
it gives wrong ans because 2008 -12- 01 lies in between 2007-01-01 and 2008-12-01. so it should not allow. if i give 2009-01-01 it has to allow. it should not allow if the month is same.
for example if i give 2008-12-02 it not lies between 2007-01-01 and 2008-12-01 but month is same.so it should not allow.so this is my critical need.so please help me to solve this.

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-14 : 00:45:25

please any one reply me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 02:13:25

ok. you had confuse me. can you state the criteria again ? When is Allow ? When is not allow ?

quote:
it gives wrong ans because 2008-12-01 lies in between 2007-01-01 and 2008-12-01

where is 2007-01-01 comes from ? the fromdate is 2008-10-01 !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 02:13:26

ok. you had confuse me. can you state the criteria again ? When is Allow ? When is not allow ?

quote:
it gives wrong ans because 2008-12-01 lies in between 2007-01-01 and 2008-12-01

where is 2007-01-01 comes from ? the fromdate is 2008-10-01 !

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 02:21:27

[code]-- prepare sample data
declare @t table (empno varchar, fromdate datetime, todate datetime)

insert @t
select 'E', '2007-01-01', '2007-03-01' union all
select 'E', '2007-04-01', '2007-07-01' union all
select 'E', '2007-08-01', '2008-08-01' union all
select 'E', '2008-10-01', '2008-12-01'

-- initialize query
declare @dt datetime

select @dt = '2008-12-01'

-- Show the result
select empno,
fromdate,
todate,
case
when @dt between low and high then 'Not Allow'
else 'Allow'
end
from (
select empno,
fromdate,
todate,
dateadd(month, datediff(month, 0, fromdate), 0) as low,
dateadd(month, datediff(month, -1, todate), -1) as high
from @t
) as x[/code]

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-14 : 02:30:36

Hmmm . . . is this a bug ? I remember only pressing the Submit Reply once.

Note : I am not trying to increase my post count by posting duplicate reply twice.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-14 : 02:35:29

Yeah, sure...
Now we know how you boost your post-count!

Peter Larsson
Helsingborg, Sweden

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2007-03-14 : 06:32:25

hi Peso thanks for cute reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources