Listing Records From Two Years Using Month

Question

0.00/5 (No votes)

See more:

Using the date below;

SN NAME STARTDATE ENDDATE

1. BABS 01/04/2011 03/06/2012
2. JKK 10/10/2011 23/12/2012
3. HAN 04/04/2011 06/02/2012
4. LOL 16/05/2011 05/05/2012

Assuming am search for record that has month and year of 05/2011

With the following code;

SELECT * FROM mytable WHERE 05 BETWEEN MONTH(StartDate) AND MONTH(EndDate) AND
(YEAR(StartDate) = 2011 OR YEAR(EndDate) = 2011)

Executing the statement above the record shown is

SN NAME STARTDATE ENDDATE

1. BABS 01/04/2011 03/06/2012
2. JKK 10/10/2011 23/12/2012
4. LOL 16/05/2011 05/05/2012

Record 3 is not showing, which suppose to be included. Looking at Record 3. the month(startdate) is less then the month(enddate) (04<02) How can I handle a record where the starting month is less than the ending month?

Posted 8-Jul-11 20:42pm

KAY ADEYEMI

Add a Solution

5 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)

Christian Graus · Answer 1 · 2011-07-08T20:45:00

Solution 1

http://www.w3schools.com/sql/sql_between.asp[^] In other words, you can say you want the date between two dates explicitly.

Posted 8-Jul-11 20:45pm

Christian Graus

Comments

harish85 9-Jul-11 2:49am

Nice link, I never noticed w3schools had links apart from html or html related technologies.
Thanks. <5.

harish85 · Answer 2 · 2011-07-08T21:25:00

Solution 3

This is just alternate "idea" that you could use..

select * from mytable where (DATEDIFF(yy,StartDate,EndDate)*1000 + DATEDIFF(mm,StartDate,EndDate)) > 2004

(Date difference greater than 2 yr and 4 months)

or

get "YYYYMM" together to compare

Disclaimer - these are only general ideas- non technology specific.

Posted 8-Jul-11 21:25pm

harish85

Comments

Christian Graus 9-Jul-11 3:27am

But she doesn't want to check the size of the date difference, she wants the value to be inside a range.

harish85 9-Jul-11 3:36am

nevermind.still didnt get OP's problem, either I am too tired, or I am not understanding it. Sorry about that post.
btw how did you make out OP as "She" and "He" ?

Christian Graus 9-Jul-11 3:46am

I assumed Kay is a girls name, could be wrong.

Philippe Mori · Answer 3 · 2011-07-09T07:47:00

Solution 5

You can also uses tool to builds you SQL request like those in Visual Studio or LINQPad and then look at the generated SQL.

Posted 9-Jul-11 7:47am

Philippe Mori

KAY ADEYEMI · Answer 4 · 2011-07-08T20:54:00

Solution 2

Christain Tks, but that is not applicable to date. In a situation the searching month falls between two dates of the same year the sql query work, but when with diffrent years and also if the month of starting date is less than month of end date it does not work

Posted 8-Jul-11 20:54pm

KAY ADEYEMI

Comments

Christian Graus 9-Jul-11 3:18am

Please don't push 'answer' to ask questions. You are wrong. The between keyword works with dates. Rereading your code, your issue is that you're using the between keyword only on the month, and adding a check for 2011, which is obviously not future proof. Use the Between keyword on the entire dates, against the full date object you want to compare, and it will work. If you ask for advice, do what people advise you before telling them that they, and all the online documentation, are wrong.

Philippe Mori 9-Jul-11 13:45pm

As mentionned by Christian, you must compare the whole date at once.

KAY ADEYEMI · Answer 5 · 2011-07-08T21:43:00

Solution 4

Narish85 Thanks, This is not working at all

Posted 8-Jul-11 21:43pm

KAY ADEYEMI

Comments

Christian Graus 9-Jul-11 3:44am

Because it does not solve your problem. My answer does. Again, DON'T PUSH ANSWER TO ADD COMMENTS !!! If you can't follow basic rules of etiquette, or follow basic instructions, then what is the point in us trying to help you ? See how harish85 and I both use the 'comment' button to add comments ? you can do it. Learn something !! Try to behave acceptably and follow the rules of the site !!!

Christian Graus 9-Jul-11 4:27am

I can see now you've asked this twice, and despite being told exactly what to do, you insist on doing it wrong, then asking us to fix it. Is this some sort of practical joke ? Why can't you just do what you've been told ?