MS SQL Query Across Year Fails

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:

PSEUDO:
WHERE
    somefield.date BETWEEN '12/01/10' AND '01/03/11'

I have been trying variants of:

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.

WHERE somefield.date BETWEEN ‘2010-12-01’ AND ‘2011-01-03’

:cool:

Thanks Rudy, I’ll give that a shot.

Rudy,

Your suggestion, of course, worked like a charm.

A thick head with Rockwell hardness off the scale is not a good thing. Must work to abandon both.

Someday I’ll learn that it’s rarely a Zebra - and usually a Horse…

Thank you.