www.webdeveloper.com
Results 1 to 2 of 2

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

  1. #1
    Join Date
    Jul 2008
    Posts
    92

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

    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...
    Last edited by mith36; 06-19-2012 at 01:36 PM.

  2. #2
    Join Date
    Jul 2008
    Posts
    92
    Problem solved: use HOUR not HOURS in DATE_ADD.

    Code:
    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles