The sql query is not giving me any results please help

SELECT * FROM [AcadAdmin].[dbo].[TempAcadTotalFees] Where [Date]>= cast('20030101' as datetime) and [Date]< cast('20100101' as datetime).

If I just use the fist condition it works but if I use both condition it doesnt give me any result. Please help.

Thank you.
dualtechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

drunkenmsCommented:
Ok, what results do you have when you use only second conditition?
Just try select queries with separate condititions.
If it doesn't happening, just look to table field "Date" values.
0
dwkorCommented:
I assume [Date] is either datetime or date type in the database?

Can you try:
SELECT * FROM [AcadAdmin].[dbo].[TempAcadTotalFees] Where [Date] between '2003-01-01' and '2010-01-01'
0
Rajkumar GsSoftware EngineerCommented:
What is the datatype of the field 'data' ?

Raj
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Rajkumar GsSoftware EngineerCommented:
If it is datetiem, it should work. (See the attached code)

I assume, it is someother datatype.

Raj
CREATE TABLE #TempAcadTotalFees
(
	Date	datetime
)

INSERT INTO #TempAcadTotalFees
	SELECT '01/01/2003' UNION
	SELECT '01/01/2004' UNION
	SELECT '01/01/2005' UNION
	SELECT '01/01/2006' UNION
	SELECT '01/01/2007' UNION
	SELECT '01/01/2008'

SELECT * FROM #TempAcadTotalFees

SELECT * FROM #TempAcadTotalFees
	Where Date >= cast('20030101' as datetime) and Date < cast('20050101' as datetime)

DROP TABLE #TempAcadTotalFees

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
Small spelling mistake in my post -  

If it is datetiem  ~   If it is datetime


Raj
0
dervishiCommented:
First we have to know datatype of field [data]!
0
dualtechAuthor Commented:
the date is datetime. and yes it contains both date and time in it. I have tried cast with it before for only one day and it was working fine. but if i try to select range then it is failing. please let me know if I am not clear. Thank you for you help.
0
dualtechAuthor Commented:
Sorry guys but i tried all your solutions but none of those are working for me
0
dualtechAuthor Commented:
declare @x datetime
set @x = cast('20100101' as datetime)
Select  *
  FROM [AcadAdmin].[dbo].[AcadAccountingCashier]where Date < @x and
Date < dateadd(dd, 1, @x)

This is the query by which I am able to fined out the data with the previous dates but I am not able to find out the data in range of dates.
0
Rajkumar GsSoftware EngineerCommented:
Do you get right result in my sample script ?

Raj
0
drunkenmsCommented:
Ok, you also can use BETWEEN ... operator for WHERE condititions for the same types. As example i will show to you the simple query, that working on my PC with fields datetime:

select * from products where date_create between '2009-10-01' and '2009-10-15'

As you can see, i'm not using casting function, cause mysql automatically trying casting MAX and MIN values for type of the `date_create` field. I think, if that query doesn't help to you, you must check available date values in your table.
Some citate from mysql manual:
"
... expr BETWEEN min AND max ..

If expr is greater than or equal to  min and expr is less than or equal to max, BETWEEN  returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the  same type. Otherwise type conversion takes place according to the rules  described at the beginning of this section, but applied to all the three  arguments. Note: Before MySQL 4.0.5,  arguments were converted to the type of expr instead.
....
"

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.