MS SQL Query Across Year Fails
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:
I have been trying variants of:
somefield.date BETWEEN '12/01/10' AND '01/03/11'
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.
table.run_date BETWEEN CONVERT(CHAR(8), '12/01/10', 1) AND DATEADD(Day, +35, CONVERT(CHAR(8), '12/01/10', 1))
CONVERT(CHAR(10), table.run_date, 101) BETWEEN CONVERT(CHAR(10), '12/01/2010', 101) AND CONVERT(CHAR(10), '01/03/2011', 101)
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.