Issue comes while search record according to year.

Question

0.00/5 (No votes)

See more:

I guys i have a table expenses with multiple records.

SQL

expenseID       ratename         description          workcompleted

51699   	Base Rate	SNL Financial inquiry	08/09/2011
51699   	Base Rate	SNL Financial inquiry	08/19/2011
51699	        Base Rate	SNL Financial inquiry	08/09/2012
51699   	Base Rate	SNL Financial inquiry	08/19/2012

But when i search record only for year 2012 but it return record for both the year(2011, 2012). But i need my record comes by year only i.e.


51699	Base Rate	SNL Financial inquiry	08/09/2012
51699	Base Rate	SNL Financial inquiry	08/19/2012

Please see my query :


select expenseid,ratename,description,workcompleted from EXPENSES 
where workcompleted >= '08/08/2012' and workcompleted <= '08/19/2012'

I also try this by using between query but it doesn't work. If any body have an idea kindly share to me.

Posted 5-Dec-12 18:10pm

Mas11

Add a Solution

2 solutions

Solution 2

Try this I hope it will help you

SQL

select expenseid,ratename,description,workcompleted from EXPENSES 
where year(workcompleted)='2012'

keep smiling:):):) happy coding:)

Posted 5-Dec-12 18:32pm

IpsitaMishra

Updated 5-Dec-12 18:33pm

v2

Comments

Mas11 6-Dec-12 1:00am

Thanks for the answer.

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)

Aarti Meswania · Accepted Answer · 2012-12-05T18:27:00

Solution 1

when ever compare dates ,
in where condition pass value in 'yyyy-MM-dd' format

SQL

select expenseid,ratename,description,workcompleted from EXPENSES
where workcompleted >= '2012/08/08' and workcompleted <= '2012/08/19'

Happy Coding!
:)

Posted 5-Dec-12 18:27pm

Aarti Meswania

Comments

Mas11 6-Dec-12 0:45am

I hope in this way i have to do big changes in my database. There is 20000 record & date comes mm/dd/yyyy format (as per my need it has to be same format). But according to your query i have to convert this in yyyy/mm/dd. There is any other solution rather then converting this.

Aarti Meswania 6-Dec-12 0:56am

hey no man

do not do that

'workcompleted' column is having datatype datetime then
just fire this query in database not required little change

ok dear
:)

Mas11 6-Dec-12 0:59am

Thanks Aarti, but now i got the solution actually my datatype is nvarchar, so it create a problem. Now i change this into datetime & problem solve. Thanks for your support.

Aarti Meswania 6-Dec-12 1:00am

welcome :)