Simple Query Question

I have a table with these three columns:

archiveYear (char,4)
archiveMonth (char,2)
archiveDay (char,2)

They contain elements for the date. ArchiveMonth and ArchiveDay use doubleDigit characters like "01, 02, 03, etc." So when written out they can read "12/03/2004" which is the datetime format. For sorting purposes, I need to combine the three tables into one (archiveMonth/archiveDay/archiveYear). Then I can sort it and call what I need easier. I assume that this can be done with the CAST function. Anybody know?

Well I could be wrong, but I think you need a cursor to do this (if you are going through each record in the table). For the cursor you will need variables to represent the day, month and year. Then for each record, combine the values of each varaible and the "/", casting the result as datetime.

I'm sure you can do it quite easily by just doing something like this:

ORDER BY archiveDay + '/' + archiveMonth + '/' + archiveYear

if not try looking at the convert function.

Actually, CONVERT would be the idea, I think. But I'm not sure how to do the query with CONVERT. The plan would be to do this:

SELECT *, CONVERT(archiveDay + '/' + archiveMonth + '/' + archiveYear) AS recordDate
WHERE recordDate >= now
ORDER BY recordDate ASC

Something like that...but I'm not formatting the CONVERT correctly. Any suggestions?