[RESOLVED] help... SQL variables, time, self-join

[mith36]

Hi. I'm having trouble constructing a mySQL query that will look at the current row, grab the date, then add three hours to that time and look at the value of the row at that different time. I am returned a syntax error, but I am not sure why. This is my first time using variables in an sql query. Should I be using some sort of self-aliasing join ?

SELECT (@dd:=CONCAT(zdate," ",zhr,":00:00")) AS date, (SELECT distinct ospd from mavsmf where CONCAT(zdate," ",zhr,":00:00") LIKE (DATE_ADD(@dd, INTERVAL 3 HOURS))) FROM mavsmf

Edit 1:
BTW, "SELECT (@dd:=CONCAT(zdate," ",zhr,":00:00")) AS date FROM mavsmf" part works fine. @dd is set as a datestring.

Edit 2:
Oh.. hmm, i guess `CONCAT(zdate," ",zhr,":00:00")` doesnt exist as a column. Is that the problem? If so, how can I do this sort of query? :/

Edit 3:
"SELECT distinct ospd from mavsmf where CONCAT(zdate," ",zhr,":00:00") LIKE '2006-01-01 06:00:00';" Worked fine - so that only leaves the DATE_ADD being a problem. Hmm...

Edit 4:
using CAST (@dd as DATE) didn't help...

[mith36]

Problem solved: use HOUR not HOURS in DATE_ADD.

SELECT (@dd:=CONCAT(zdate," ",zhr,":00:00")) AS date, (SELECT distinct ospd from mavsmf where CONCAT(zdate," ",zhr,":00:00") LIKE (DATE_ADD(@dd, INTERVAL 3 HOUR))) FROM mavsmf