• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 449
  • Last Modified:

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.
0
dualtech
Asked:
dualtech
  • 4
  • 3
  • 2
  • +2
1 Solution
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now