[RESOLVED] Select between 2 "dates"

[stanav]

Hello guys,

I have a DB table where the date is represented in 6 separate integer fields/columns (year, month, day, hour, minute, second). Don't ask me why and please do not tell me to change the column types... It's something I have no control over. Now my question is, how do I create a query to select all records between 2 dates in this table?
I've tried this but it doesn't return the right records.

"Select * from tablename where (yr >= @year1 and month >= @month1 and day >= @day1) 
                                   and (yr <= @year2 and month <= @month2 and day <= @day2)

Any help is greatly appreciated.

[GaryMazzone]

What about converting to string (varchar) and then Convert to DateTime \

CONVERT(DATETIME(CONVERT(VARCHAR(2),month) + '/' + Convert(VARCHAR(2),day) + '/' + Convert(Varchar(4),Year) )

[jmcilhinney]

SELECT * FROM TableName
WHERE (yr > @year1 OR (yr = @year1 AND (month > @month1 OR (month = @month1 AND day >= @day1)
AND (yr < @year2 OR (yr = @year2 AND (month < @month2 OR (month = @month2 AND day <= @day2)

[stanav]

@JMC: Thanks, JMC. That did the trick.

@Gary: your suggestion probably works in most cases but the problem is the DB I'm working with (there are some records that that the "day" field has no data and thus it default to zero - so problems arise when it tries to convert "12/00/2010" to a date Using JMC's query, I was able to pull these records and fix them as needed. Thanks anyway.