Compare two dates with another two dates in SQL Server

Question

I want to filter data from SQL Server 2008 R2 using FromDate and ToDate with another 2 user input dates. I want query for that.

I want to filter data such a way that If user input 2 dates or any date in between of them lies in FromDate or 'ToDate' or in between date then it filters data.

For i.e.

 FromDate             ToDate
15-11-2014          20-11-2014

if user input dates are 11-11-2014 and 20-12-2014. It means in between dates of this 2 dates lies in between FromDate and ToDate so it should return this record.

Now, if user input dates are 11-11-2014 and 14-11-2014. It means in between dates of this 2 dates does not lie in between FromDate and ToDate so it should not return this record.

It must match given dates and as well as in between dates.

Nima MashhadiZadeh · Accepted Answer · 2014-11-12 07:07:03Z

1

you Can use this :

Where

DATEADD(dd, 0, DATEDIFF(dd, 0, FromDate)) > '15-11-2014'

and

DATEADD(dd, 0, DATEDIFF(dd, 0, ToDate)) < '20-11-2014'

answered Nov 12, 2014 at 7:07

Nima MashhadiZadeh

1671 gold badge6 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ajay2707 · Accepted Answer · 2014-11-12 06:19:23Z

0

please check the example, you just compare the parameter with both datetime column to extract the rows.

declare @table table(id int,frdt datetime, todt datetime)

insert into @table values (1,GETDATE()-20, GETDATE()-19)
,(1,GETDATE()-9, GETDATE()-8)
,(1,GETDATE()+20, GETDATE()+18)
,(1,GETDATE(), GETDATE()-1)
,(1,GETDATE()-20, GETDATE())
,(1,GETDATE()-10, GETDATE()+10 )

select * from @table

declare @frdt datetime = null , @todt datetime = getdate()-10

select @frdt, @todt,* from @table
where
 (@frdt is null or @frdt between frdt and todt)
and
 (@todt is null or @todt between frdt and todt)

 select @frdt = GETDATE() , @todt = GETDATE()

 select @frdt, @todt,* from @table
where
 (@frdt is null or @frdt between frdt and todt)
 and 
 (@todt is null or @todt between frdt and todt)

answered Nov 12, 2014 at 6:19

Ajay2707

5,8286 gold badges44 silver badges61 bronze badges

2 Comments

MONEYMSN Over a year ago

where you got the answer for this?

Ajay2707 Over a year ago

@MONEYMSN, I don't remember, but what do you want from me or us? Have any question or query, then post your question or comment here with example.

Vineeth Vijayan · Accepted Answer · 2014-11-12 06:42:00Z

0

From what I understand from your question.

select 'a' A, CONVERT(date,'11/15/2014') FromDate, CONVERT(date, '11/20/2014') ToDate into #Temp

declare @frmDate date; 
declare @toDate date;

set @frmDate = Convert(date, '11/11/2014');
set @toDate = CONVERT(Date, '12/20/2014');

select * from #Temp where FromDate>=@frmDate and ToDate<=@toDate

set @frmDate = Convert(date, '11/11/2014');
set @toDate = CONVERT(Date, '11/14/2014');

select * from #Temp where FromDate>=@frmDate and ToDate<=@toDate

Hope fully it help you :)

edited Nov 12, 2014 at 6:42

answered Nov 12, 2014 at 6:33

Vineeth Vijayan

1,3193 gold badges20 silver badges36 bronze badges

4 Comments

CSAT Over a year ago

this is not the thing im looking for. please read my post properly.

Vineeth Vijayan Over a year ago

Are you expecting 1 input or 2 from user? @CSAT.

CSAT Over a year ago

think that there is one row with id and 2 dates. and i want to compare it based on user input dates. and it should fulfill requirements as per details in post.

Vineeth Vijayan Over a year ago

I still don't get it. Sorry @CSAT :(

Collectives™ on Stack Overflow

Compare two dates with another two dates in SQL Server

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related