What kind of database are you using???
Why did you separate day, month and year? It's fine if your date ranges
never cross month or year boundaries, but you're going to have a hard time
querying a range like 2003-12-26 -> 2004-01-03.
Bad column name choices too, by the way. Three of your four columns are
reserved words.
Anyway. You can't reliably get a list from a set of columns, as this
violates set theory. (Databases are used to store sets of data. If you are
not storing sets, but rather lists, just use a flat file.) Granted, there
are ways to do it, but this is better handled in the application.
First, you have to figure out when Sunday was, then return a set of ROWS
that fall in that category.
sunday = dateadd("d", 1-datepart("w", date()), date())
sunday = year(sunday) & "-" & month(sunday) & "-" & day(sunday)
sql = "SELECT [day], [month], [year], message FROM sometable WHERE CDate("'"
& [year] & "-" & [month] & "-" [day] & "'") >= CDate('" & sunday & "')"
set rs = conn.execute(sql)
Then, use ASP to translate the rows into a list,
thislist = ""
prevday = -1
do while not rs.eof
if rs(0) <> prevday then
response.write thislist & "<p>"
prevday = rs(0)
response.write rs(2) & "-" & rs(1) & "-" & rs(0) & ":"
thislist = rs(3)
else
thislist = thislist & ", " & rs(3)
end if
rs.movenext
loop
response.write thislist & "<p>"
