Retrieving information - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Retrieving information

Author

Topic

e.bar
Starting Member

25 Posts

Posted - 2003-04-22 : 17:03:01

Why the statement below returns 2001, 2002 and 2003 results?

select Customer
from dbo.Customer INNER JOIN dbo.Purchases ON dbo.Customer.CustomerID = dbo.Purchases.PurchaseID
where (convert(varchar,PurchaseDate,103) BETWEEN '1/1/2000' and '31/12/2000')

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2003-04-22 : 17:14:14

Try...

select Customer
from dbo.Customer INNER JOIN dbo.Purchases ON dbo.Customer.CustomerID = dbo.Purchases.PurchaseID
and datepart(yyyy, PurchaseDate) = 2000

Justin

"Take care of yourself and do night fight with pygmies" - RT

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 17:17:42

you are converting you date into a varchar, which is a TEXT datatype and is sorted that way. Remember that we sort text different than numbers, and those differently from dates.

For example, take following data: 1,11,2,23

As numbers, it sorts like this:

1
2
11
23

As TEXT, it sorts like this:

1
11
2
23

Does that make sense? when it's text, replace the 1's with "a" and the 2's with "b" and see if that helps.

The solution? don't convert your date to a varchar. Just leave as is:

select Customer
from dbo.Customer INNER JOIN dbo.Purchases ON dbo.Customer.CustomerID = dbo.Purchases.PurchaseID
where PurchaseDate BETWEEN '1/1/2000' and '31/12/2000'

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-22 : 17:18:29

For date and time queries use the date functions instead of BETWEEN and others. DATEPART, DATEADD, etc...

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-22 : 17:29:01

quote:

For date and time queries use the date functions instead of BETWEEN and others. DATEPART, DATEADD, etc...

I am not sure about this (sorry Tara!)... do not manipulate your columns using functions and then filter that way -- you will not be able to make use of indexes. There is no problem using BETWEEN and all other comparison operators with date/time queries, you just need to make sure you are doing things in the proper format.

Compare the following execution plans if the date field has an index:

Select *
from table
where Year(date) = 2002

vs

select *
from table
where date between '1/1/2002' and '12/31/2002'

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-22 : 17:48:18

Hey no reason to apologize. I have read that you should use the date functions instead, so that's why I mentioned it. I believe that what I read was for LIKE comparisons as well as others, but I could be recalling this incorrectly.

I'll keep it in mind the next time that I work with these types of queries.

Tara

e.bar
Starting Member

25 Posts

Posted - 2003-04-23 : 08:44:43

Ok, tks for all replies, but:

1) I need retrieve information based on periods, where it does not begin on first day or end on thirtieth. My period can begin on fifteen day, so I need information between 15/3/2003 and 14/4/2003.

2) I´m in Brazil, so the users type dd/mm/yyyy format.

3) e.g. In other search forms with one date, users type "31/12/2002" and the results are "31/12/2002" because the sql statement on results page is "WHERE CONVERT(char(10),PurchaseDate,103).

4) I would like to use "BETWEEN". Using DATEPART I´ll need dd, mm & yyyy DATEPARTS for two dates.

More ideas? Tks again!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 09:02:21

???

So this does not return correct results ???

select Customer
from dbo.Customer INNER JOIN dbo.Purchases ON dbo.Customer.CustomerID = dbo.Purchases.PurchaseID
where PurchaseDate BETWEEN '1/1/2000' and '31/12/2000'

- Jeff

e.bar
Starting Member

25 Posts

Posted - 2003-04-23 : 09:43:33

No Jeff, it does not return correct results. I get:

Server: Msg 296, Level 16, State 3, Line 1
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

Edited by - e.bar on 04/23/2003 09:45:21

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 09:55:15

Is your database set up for the dd/mm/yyyy format?

To avoid any confusion, many people here suggest always using this format:

yyyy-mm-dd

because there is never confusion about that one.

Clearly, the error you are receiving is that your DB is interpreting 31/12/2000 as month 31, day 12, year 2000 -- which is out of range, of course.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 10:24:16

Also, consider this:

select Customer
from dbo.Customer INNER JOIN dbo.Purchases ON dbo.Customer.CustomerID = dbo.Purchases.PurchaseID
where PurchaseDate
BETWEEN convert(datetime, '01/01/2000',103) AND
convert(datetime, '31/12/2000', 103)

The main problem you had in your original query is that you were CONVERTING the wrong part of the expression.

- Jeff

e.bar
Starting Member

25 Posts

Posted - 2003-04-23 : 11:03:10

Hey Jeff, I was really trying to convert the wrong part. It worked. Thank you for your support!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources