question about convert function 2

[alarge23]

The query below should only be pulling records from March of 2005. Instead it pulls records from March 2004 and March 2005. Why?

select *
from tablename
WHERE Convert(char(12), date_submitted, 101) BETWEEN '03/01/2005' AND '03/31/2005'

However, if I switch and use format 111 it works? I could just use this one, but I don't understand why the above query will not work. Any help would be appreciated.

select *
from tablename
WHERE Convert(char(12), date_submitted, 111) BETWEEN '2005/03/01' AND '2005/03/31'

 

[tc3596]

I'm surprised anything worked since 101 and 111 both have yy formats. Should be char(8) anyway.

 

[SQLBill]

tc3596....

101 and 111 have yyyy formats. Any style with 1xx is the four digit year...so 01 is yy 101 is yyyy, 11 is yy and 111 is yyyy.

alarge23,

Gotta ask it....is that the actual code you used? Because I don't see any problems with it except for the first one you use "mm/dd/yyyy" and the second one you use "yyyy/mm/dd" in the WHERE clause.

Can you run this and give us the result?

SELECT COUNT(date_submitted)
FROM tablename
WHERE date_submitted <= '2004-12-31'
GO
SELECT COUNT(date_submitted)
FROM tablename
WHERE date_submitted >= '2005-01-01'
GO

-SQLBill

Posting advice: FAQ481-4875

 

[tc3596]

sqlbill,
you are right

 

[JamesLean]

The problem is arising from the fact that you are converting your date column values to char to do the comparison. This means that they will be sorted alphabetically and not by actual date.

eg, the following character values are sorted correctly:

02/27/2008
[red]03/01/2005[/red]
03/04/2002
03/29/2000
03/31/2004
[red]03/31/2005[/red]
04/01/2002

Your query returns all the values between the two red values, ie any date in march from any year.

Get rid of the conversion and it should be fine. Also note the use of unambiguous date formats:

SELECT *
FROM tablename
WHERE date_submitted >= '20050301'
  AND date_submitted < '20050401'

--James

 

[r937]

nicely written, james -- star for you

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[alarge23]

James, Thanks for the tip that worked!