Error in SQL Query - 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

Other Forums

Other Topics

Error in SQL Query

Author

Topic

Sudi
Starting Member

1 Post

Posted - 2002-05-16 : 07:25:30

I am getting date value from textbox in dd/mm/yy format
(i)when i pass '10/01/02' and '16/05/02' to the select query I get the right output
(ii)when i pass '12/01/02' and '16/05/02' to the select query I get the wrong output
select * from tb_contactus where convert(char(8),CODate,3) between '10/01/02' and '16/05/02' Order by CAST(SrNo as INT)

SrNo Date Email
1 5/10/2002 power@power.com
2 5/10/2002 power@power.com
3 5/10/2002 power@power.com
4 5/10/2002 power@power.com
5 5/10/2002 power@power.com
6 5/10/2002 power@power.com
7 5/10/2002 power@power.com
8 5/10/2002 power@power.com
9 5/10/2002 power@power.com
10 5/10/2002 power@power.com
11 5/10/2002 power@power.com
12 5/10/2002 power@power.com
13 5/10/2002 power@power.com
14 5/10/2002 power@power.com
15 5/10/2002 power@power.com
16 5/10/2002 power@power.com
17 5/10/2002 power@power.com
18 5/10/2002 power@power.com
19 5/10/2002 power@power.com
20 5/10/2002 power@power.com
21 5/10/2002 power@power.com
22 5/15/2002 w@w.com
23 5/15/2002 ee@ee.com

select * from tb_contactus where convert(char(8),CODate,3) between '12/01/02' and '16/05/02' Order by CAST(SrNo as INT)

SrNo Date Email
22 5/15/2002 w@w.com
23 5/15/2002 ee@ee.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-16 : 08:03:43

You might find this helpful:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=13160

SQL Server will always favor a US mm/dd/yyyy date interpretation wherever it can. You might want to consider changing the format to yyyymmdd, STRICTLY for inserting into the database (display it any way you want in the textbox, but reformat it BEFORE you insert it into SQL Server)

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-16 : 08:25:49

When you perform the convert(char(8), CODate, 3), your results end up as a char datatype. So you end up comparing 10/05/02 and 15/05/02 to your dates (10/01/02, 16/05/02, 12/01/02). When SQL compares char type strings it starts at the left and works it's way to the right. Therefore, comparing the day to the day you pass first: i.e. the 10 in 10/05/02 CoDate column is compared to the 10 or 12 you pass in the between clause. Therefore, 10 is not between 12 and 16. I think the following query will do what you want. I sure someone else here on the forum might be able to optimize this query better.

Create Procedure usp_contactus
@begin_date char(8),
@end_date char(8)
AS

Select * from tb_contactus
Where CODate Between cast(SUBSTRING(@begin_date,4,2)+'/'+LEFT(@begin_date,2)+'/20'+Right(@begin_date,2) as Datetime)
AND cast(SUBSTRING(@end_Date,4,2)+'/'+LEFT(@end_Date,2)+'/20'+Right(@end_Date,2) as Datetime)
Order by CAST(SrNo as INT)

Unless all of your dates in CODate are in the year 2000, I would suggest passing in a char(10) value in dd/mm/yyyy format allowing you to change the between dates to the following:
cast(SUBSTRING(@end_Date,4,2)+'/'+LEFT(@end_Date,2)+'/'+Right(@end_Date,4) as Datetime)

Jeremy

Sniped by robvolk