Hello.

Platform: MSSQL Server 2005.

I'm accustomed to MySQL and Oracle, and I have encountered an issue which seems trivial in nature, but I cannot seem to overcome it.

I have a database table containing a column of datetime type. I need to query against this column where the date range crosses over into the next year. Something along the lines of:
Code:
PSEUDO:
WHERE
    somefield.date BETWEEN '12/01/10' AND '01/03/11'
I have been trying variants of:
Code:
WHERE
    table.run_date BETWEEN CONVERT(CHAR(8), '12/01/10', 1) AND DATEADD(Day, +35, CONVERT(CHAR(8), '12/01/10', 1))

and

WHERE
CONVERT(CHAR(10), table.run_date, 101) BETWEEN CONVERT(CHAR(10), '12/01/2010', 101) AND CONVERT(CHAR(10), '01/03/2011', 101)
but the query will not return any data for dates later than 12/31/10 (and data does in fact exist for dates later than 12/31/10). Some variants return no data at all.

Frankly I'm mystified that a major database engine does not support something as simple as the pseudo I posted above while it is so straight forward in Oracle, MySQL..... (But that is another issue).

Any assistance/guidance as to how to accomplish this task is appreciated.