shahrul


Hi all,

I am quite surprise by getting wrong resultset from a simple query like:

select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'

In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null simply because there is no such date as 2008 yet in the DB.

Help from anyone is needed. Thanks in advance.

Sha.




Re: Query problem involving DateTime column

Kent Waldrop Se07


You should get no rows and not a null result -- and it sounds like that is what you are getting -- the expected result.






Re: Query problem involving DateTime column

Adam D. Turner

It shouldn't return any results. It will only return null values when there are null values in the table(s)

Adam







Re: Query problem involving DateTime column

shahrul

thanks for correcting my mistake. btw, any idea why the resultset still return rows when there is no such date in the DB





Re: Query problem involving DateTime column

Kent Waldrop Se07

How about listing some sample data and the actual result..





Re: Query problem involving DateTime column

Adam D. Turner

shahrul wrote:

thanks for correcting my mistake. btw, any idea why the resultset still return rows when there is no such date in the DB

This is impossible. If your terminology in the query is relative, do_date could possibly be postdated meaning there are records that exist.

However, if there are no records in 2008, the only possible explanation for it returning records is the stored date is in a strange format. (stored as varchar())

Adam






Re: Query problem involving DateTime column

shahrul

ok, this is my query (when data for this date range is available)

select DO_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2007' and do_date <= '6/31/2007'

results in:

DO_No

----------

1007
1007
1012
1012
1020
1020
1030
1030
1047
1047

This result is fine.

but, when I query something nonsense (data not available in DB) like:

select DO_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'

or

select DO_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/894794' and do_date <= '6/31/223424'

will result in:

DO_No

-----------

1012
1012
1030
1030
1047
1047

What may cause this It supposed to return no row.




Re: Query problem involving DateTime column

Adam D. Turner

As I have said previously, your date field is in a generic format with a generic datatype such as varchar(30).

What datatype is your date field

Run this query and post the results set.

SELECT TOP 10 do_date

FROM Delivery_Order

WHERE LEN(do_date) > 10

ORDER BY LEN(do_date) DESC

Adam






Re: Query problem involving DateTime column

shahrul

datetime type

6/16/2007 12:00:00 AM
6/16/2007 12:00:00 AM
6/15/2007 12:00:00 AM
6/15/2007 12:00:00 AM
6/14/2007 12:00:00 AM
6/14/2007 12:00:00 AM
6/14/2007 12:00:00 AM
6/15/2007 12:00:00 AM
6/15/2007 12:00:00 AM
6/14/2007 12:00:00 AM

maybe i should include time in the query as well to get expected resultset what's the correct way to retrieve data within specific time range when dealing with datetime column thanks.





Re: Query problem involving DateTime column

Adam D. Turner

I see. It still shouldn't work.

Try this and please post the result set.

select Top 5 do_date from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'

...or try to select the top 5 do_dates of the other strange dates you have.

Adam






Re: Query problem involving DateTime column

shahrul

this time error occurs:

Msg 242, Level 16, State 3, Line 1

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.





Re: Query problem involving DateTime column

Manivannan.D.Sekaran

Try this,

Code Block

select

DO_No

from

Delivery_Order

where

cust_id = 5

and isdate(do_date) = 1

and cast(do_date as datetime) >= '6/15/2007'

and cast(do_date as datetime) <= '6/31/2007'






Re: Query problem involving DateTime column

shahrul

hi adam and mani,

thanks for ur suggestions. nothing is wrong with both ways. the error occurs because for datetime datatype, it will automatically(wow) validate the date entered, I assumed from my tests. All this while, we were trying with june 31st as the end date while there is no way June has 31 days. Thus, giving us 'Out-of-range' date.

so, i need to do extra checking to know how many days are in the month selected, as well as determining whether the year is a leap year (29 days in Feb).

Thanks all.