Get Records based on month and year

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi all

i have two combo box where i added all the months name's first 3 letters(e.g. Jan, Feb etc.)
and another combo box which is having all the years from 2005-2012

Now i wrote the below query to find the result between 2 months of a year
but it's not giving me the output.

Please tell me where i am going wrong

SQL

select DatePart(MM,FromDate) as Month,Sum(Amount) as Amount 
from SuppReport where DatePart(MM,FromDate) between 
DATEPART(MM,CAST('Jan'+ '2005' AS datetime) and DATEPART(MM,CAST('Dec'+ '2005' AS datetime)
and CONVERT(VARCHAR(4),
DateName(YEAR,FromDate))='2005' Group By DatePart(MM,FromDate)

In this query i am trying to convert month name to month number.
but showing error

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'and'.

Posted 5-Dec-13 1:34am

Snehasish_Nandy

Updated 5-Dec-13 17:45pm

v3

Add a Solution

1 solution

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)

Mehdi Gholam · Answer 1 · 2013-12-05T01:38:00

Solution 1

Use DATEPART instead, see here : http://www.w3schools.com/sql/func_datepart.asp[^]

and also you should use numeric months not 'Feb' and 'Apr' (you can do it with the DATEPART function).

Posted 5-Dec-13 1:38am

Mehdi Gholam

Comments

[no name] 5-Dec-13 7:39am

Thanks Mehdi. but i have to do like this only..please help me out..

Mehdi Gholam 5-Dec-13 7:44am

Why?

[no name] 5-Dec-13 7:45am

Now this is how my company ppl asked me to do..can't help it..They already did single month, year and date wise..i have to add from and to function

Mehdi Gholam 5-Dec-13 7:54am

Just change the where clause to work with DatePart, it's simpler and works better.

[no name] 5-Dec-13 7:56am

Then how to convert the month from lets say Jan to 01

Mehdi Gholam 5-Dec-13 9:13am

Datepart(mm, fromdate) between 2 and 4

[no name] 5-Dec-13 23:18pm

But my combo box are having data in Jan, Feb format..

Mehdi Gholam 6-Dec-13 0:53am

Keep that part.

Maciej Los 5-Dec-13 17:57pm

+5!

Mehdi Gholam 6-Dec-13 0:52am

Thanks Maciej!