compare dates

Question

Post reply

compare dates

Prakash-485822

SSC Eights!

Points: 982
More actions
March 21, 2010 at 2:52 am

#103912

Dear All,
Please find the following sample data
FId PId FromDate Todate
31 62010-03-21 00:00:00.0002010-03-27 00:00:00.000
31 42010-03-28 00:00:00.0002010-03-31 00:00:00.000
31 22010-03-29 00:00:00.0002010-03-30 00:00:00.000
i using the following query
Declare @FromDate DateTime
Declare @ToDate DateTime
Set @FromDate='2010-03-24 00:00:00.000'
Set @ToDate='2010-03-25 23:59:59'
Select * from dbo.agcorpleaveplandates
where PId=31
AND (fromdate BETWEEN @FromDate AND @ToDate)
OR (ToDate BETWEEN @FromDate AND @ToDate )
The Output i am expecting is
FId PId FromDate Todate
31 62010-03-21 00:00:00.0002010-03-27 00:00:00.000

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi Prakash,

either the column names you provided is wrong or you're using the wrong column (PId=31).

Other than that I guess you want to find the rows where your parameters are within Fromdate and Todate. If so, try the following:

SELECT *

FROM @tbl

WHERE PId=31

AND (fromdate <= @FromDate)

AND (ToDate >= @ToDate)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

lmu92 (3/21/2010)
Hi Prakash,
either the column names you provided is wrong or you're using the wrong column (PId=31).
Other than that I guess you want to find the rows where your parameters are within Fromdate and Todate. If so, try the following:
SELECT *
FROM @tbl
WHERE PId=31
AND (fromdate <= @FromDate)
AND (ToDate >= @ToDate)

I agree with the "wrong column" aspect of this, but the AND's are quite right. Try this instead. It solves for ALL overlapping dates....

--===== Create a test table and poplulate it. This is NOT a part of the solution.

CREATE TABLE #MyHead

(

FId INT,

PId INT,

FromDate DATETIME,

ToDate DATETIME

)

INSERT INTO #MyHead

(FId,PId,FromDate,Todate)

SELECT '31','6','2010-03-21 00:00:00.000','2010-03-27 00:00:00.000' UNION ALL

SELECT '31','4','2010-03-28 00:00:00.000','2010-03-31 00:00:00.000' UNION ALL

SELECT '31','2','2010-03-29 00:00:00.000','2010-03-30 00:00:00.000'

--===== Solve for overlapping dates

DECLARE @FromDate DATETIME,

@ToDate DATETIME

SELECT @FromDate = '2010-03-24 00:00:00.000',

@ToDate = '2010-03-25 23:59:59'

SELECT *

FROM #MyHead

WHERE FId=31

AND @ToDate >= FromDate

AND @FromDate <= ToDate

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Michael Meierruth SSChampion Points: 10051 More actions · Answer 3

Prakash,

Given two intervals

|------------|

e1 e2

|------------|

t1 t2

If they DON'T overlap this means e2<=t1 or e1>=t2

If they DO overlap apply NOT condition and simplify:

NOT (A or B) ---> NOT A and NOT B ---> e2>t1 and e1<t2

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

Prakash,

Sorry for the small mistake I introduced into my response.

I think the if e1, e2, t1 and t2 are all expressed to the same level of accuracy (seconds, minutes, hours, days, etc) then this is a universally accepted definition of overlapping intervals of time.

Thus here is the correct response.

Given two intervals

e1 e2

|------------|

t1 t2

|------------|

If they DON'T overlap this means e2<t1 or e1>=t2

If they DO overlap, then apply NOT condition and simplify:

NOT (A or B) ---> NOT A and NOT B ---> e2>=t1 and e1<t2

Do we all agree with that? Or am I completely off the mark?

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

OK, somebody was hawking the coffe. It's e2>=t1 and e1<=t2.