Displaying Records Beteen two dates

Question

0.00/5 (No votes)

See more:

Please can someone help me out, I need to display records that falls in between two dates inclusive using the month. Assuming data below is my data;

SN NAME FIRSTPAY_DATE RECOVERY_DATE

1. DENIS 30/06/2010 22/02/2011
2. KAY 10/03/2010 05/07/2010
3. JANE 06/01/2010 03/02/2010
4. LOLA 03/08/2010 10/10/2011

If I need to display record that falls in between FIRSTPAY_DATE AND RECOVERY_DATE using july 2010 the following record should be displayed;

SN NAME FIRSTPAY_DATE RECOVERY_DATE

1. DENIS 30/06/2010 22/02/2011
2 KAY 10/03/2010 05/07/2010

Please help

Posted 7-Jul-11 22:52pm

KAY ADEYEMI

Add a Solution

Comments

fjdiewornncalwe 8-Jul-11 7:42am

Kay. In the future, please add commments using the "Add Comment" link at the bottom right corner of the solution you are replying to so that the threads stay intact. Cheers.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Simon_Whale · Answer 1 · 2011-07-08T00:10:00

Solution 5

Select *
From tablename
Where year(@date) between year(firstpay_day) and
 year(recovery_date) and month(@date) between month(firstpay_day)
and month(recovery_date)

Give that a try

Posted 8-Jul-11 0:10am

Simon_Whale

Comments

fjdiewornncalwe 8-Jul-11 7:39am

This is the correct solution. I missed the "inclusive to the month" part on my first read-through.

fjdiewornncalwe 8-Jul-11 7:41am

OPs answer moved to comment:
Thanks Simon_Whale

This works perfectly for me.

OriginalGriff · Answer 2 · 2011-07-07T23:02:00

Solution 1

Try:

SELECT * FROM myTable where @date >= FIRSTPAY_DATE AND @date <= RECOVERY_DATE

Posted 7-Jul-11 23:02pm

OriginalGriff

Comments

fjdiewornncalwe 8-Jul-11 7:40am

OPs answer moved to comment:
Thanks OriginalGriff

But i want something like this

SELECT * FROM myTable where (YEAR AND MONTH (@date)) between (YEAR AND MONTH(FIRSTPAY_DATE)) AND (YEAR AND MONTH(RECOVERY_DATE))

Reiss · Answer 3 · 2011-07-07T23:31:00

Solution 3

This should be what you are after - not tested the performance though

declare @date datetime

set @date = '10 july 2010'

SELECT * FROM myTable where CAST(CAST(DATEPART(year, @date) as varchar(50)) + '-' + CAST(DATEPART(month, @date) AS varchar) + '-1'  AS DATETIME) between FIRSTPAY_DATE AND RECOVERY_DATE

Edit:Fixed minor spelling error

Posted 7-Jul-11 23:31pm

Reiss

Updated 7-Jul-11 23:32pm

v2

Comments

fjdiewornncalwe 8-Jul-11 7:40am

OPs answer moved to comment:
Thank Reiss

But this is not returning any record

fjdiewornncalwe 8-Jul-11 7:41am

Reiss comment to above:
Reiss - 34 mins ago
I suspect due to us having different date formats, which Simons solution handles and his would be more effecient anyway due to not having to cast anything.

KAY ADEYEMI · Answer 4 · 2011-07-08T10:00:00

Select *
From tablename
Where year(@date) between year(firstpay_day) and
year(recovery_date) and month(@date) between month(firstpay_day)
and month(recovery_date)

This suggested solution above only work for a month with the same year eg
If @date = '03/04/2011'
Fistpay_day = '01/04/2011'
Recovery_date = '29/12/2011'

BUT when @date = '03/04/2011'
Fistpay_day = '01/04/2011'
Recovery_date = '29/02/2012'

it will not work because the month in second year(29/02/2012) is less than starting year (01/04/2011)

ie o4 between 04 and 02????

Please help out

Displaying Records Beteen two dates

4 solutions

Solution 5

Solution 1

Solution 3

Solution 7

Add your solution here

Preview 0

Existing Members

...or Join us