Very Computer

First I use
SELECT convert(char(12),orddate,101) as dd FROM tblorder WHERE
convert(char(12),orddate,101) between '02/16/1999' and '07/16/2000'
I get result
DD           |
---------------
07/12/2000   |
07/12/2000   |

---------------
2 rows have been shown

Second I change date from 02/16/1999 to 07/16/1999
SELECT convert(char(12),orddate,101) as dd FROM tblorder WHERE
convert(char(12),orddate,101) between '07/16/1999' and '07/16/2000'
No matter what year I change from 1999,2000,2001, or whatever. It
returns NULL.

Can someone tell me why I have problem with year? Please help me. I'm
really new to SQL

Kit

* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
The fastest and easiest way to search and participate in Usenet - Free!

Your WHERE clause is doing comparisons on character data, not datetimes. When
you use CONVERT to "format" orddate, you are also changing the data type.

Try something more along the lines of ...

WHERE orddate >= '07/16/1999'
AND      orddate  < '07/17/2000'

---------------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be
cut and pasted into Query Analyzer is appreciated.

Quote:> I use MS SQL 6.5 Developer Edition. I have problem when I try to query
> data base on date. It seems like the query didnt care about year. I
> dont know SQL 6.5 has problems with Y2K problem or not. Please take a
> look at my query.

> First I use
> SELECT convert(char(12),orddate,101) as dd FROM tblorder WHERE
> convert(char(12),orddate,101) between '02/16/1999' and '07/16/2000'
> I get result
> DD           |
> ---------------
> 07/12/2000   |
> 07/12/2000   |

> ---------------
> 2 rows have been shown

> Second I change date from 02/16/1999 to 07/16/1999
> SELECT convert(char(12),orddate,101) as dd FROM tblorder WHERE
> convert(char(12),orddate,101) between '07/16/1999' and '07/16/2000'
> No matter what year I change from 1999,2000,2001, or whatever. It
> returns NULL.

> Can someone tell me why I have problem with year? Please help me. I'm
> really new to SQL

> Kit

> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!