HELP ... convert(varchar(10), date, 1) doesn't work w/2003! 1

[birgitd]

Hi, I use convert(varchar(10), date, 1) in a lot of my select statements to easily select all rows for a particular date, but when I use a date in 2003, I do not get any rows back. For example:

select * from enrollment where convert(char(10), add_dt, 1) between '12/01/02' and '01/06/03'

Returns NO rows, BUT

select * from enrollment where add_dt between '12/01/02' and '01/06/03'

does return data. This is a CRITICAL problem for me, as I have about 25 automated jobs that run every night that use this syntax to select the data to act upon. ANY help would be greatly appreciated!!!

Thanks,
Birgit

 

[rac2]

The sort order for strings is different that the sort order for dates.

For strings the order is
'01/06/03'
'12/01/02'

So nothing is greater than '12/01/02' and less than '01/06/03'.

Why convert the add_date to strings? As you discovered it is properly compared to '12/01/02' and '01/06/03'.

 

[birgitd]

Thanks for the answer. That solves my problem. I guess I should use style 102 unstead of style 1 in the convert.

As far as why I convert, it appears to be more accurate. For example, when I run "where add_dt between '12/01/02' and '01/06/03'" I get no rows for 1/6/03. However, if I run "where convert(varchar(10), add_dt, 102) between '2002.12.20' and '2003.01.06'", I get the twelve rows that have been added today. I know between is supposed to inclusive, but it doesn't seem to function that way on a date select.

Of course, it may be that I don't totally understand what's going on, but this is the only way I've been able to consistently get all my data when selecting by date.

Thanks again for the prompt help!

Birgit

 

[tlbroadbent]

Brigit,

The reason you don't get matches is because the datetime column in SQL server contains both date and time. You should avoid converting the column in the WHERE clasue as this will slow the query considerably if the column is indexed. When columns are converted or subjected to other functions, SQL cannot use the indexes and must perform index or table scans.

Try this query.

select *
from enrollment
where add_dt between '12/01/02' and '01/06/03 23:59:59.999'

Though the above query works, I also prefer to use a more universally acceptable date format.

select *
from enrollment
where add_dt between '2002-12-01' and '2003-01-06 23:59:59.999'
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.