Date Between Problem

[karthickkuchanur]

Hai,
I have to check the date and time ovarlapping through my query,
The problem is suppose in DB

RECORD IN DB

Startdate End Date
2008-12-01 09:00:00.0 2008-12-01 12:00:00.0

My input
2008-12-01 09:00:00.0 2008-12-01 12:00:00.0 ->date Between given input this working fine i will show the record is duplicate

My input -2
2008-12-01 08:00:00.0 2008-12-01 13:00:00.0 ->
The query i am using show it is not between


My Query is
SELECT STARTDATE,ENDDA TE FROM PRONDUTYCOMPOFF
WHERE
STATUS NOT IN('NULLIFIED') AND (? BETWEEN STARTDATE AND ENDDATE OR
? BETWEEN STARTDATE AND ENDDATE)

Please help me dont bother about status

[amitpatel66]

I am not able to understand your requirement. Can you be more clear on your requirement? Can you post actual data and sample output of your query?

[st33chen]

i think your SQL statement is :

SELECT STARTDATE, ENDDATE
FROM PRONDUTYCOMPOFF
WHERE STATUS NOT IN('NULLIFIED')
AND (
:dt_bgn BETWEEN STARTDATE AND ENDDATE
OR
:dt_end BETWEEN STARTDATE AND ENDDATE
)

note --> :dt_bgn is the 'START' part of your input
:dt_end is the 'END' part of your input

please try this :

SELECT STARTDATE, ENDDATE
FROM PRONDUTYCOMPOFF
WHERE STATUS NOT IN('NULLIFIED')
AND (
STARTDATE between :dt_bgn and :dt_end
OR
ENDDATE between :dt_bgn and :dt_end
)