SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    dayname does not recognise column

    I've looked at various other threads but can't seem to find a solution to this problem. This is the first part of the SQL statement:
    Code SQL:
    SELECT tt_learning_weeks.week_number, 
    Date_format((IF(tt_learning_weeks.week_number <200, tt_learning_weeks.start_date
     + ((tt_learning_weeks.week_number -100)*7) + tt_teaching_episodes.day_of_week1 -1,
     tt_learning_weeks.start_date + ((tt_learning_weeks.week_number -200)*7) + 
    tt_teaching_episodes.day_of_week1 -1)) , '%Y-%m-%d') AS theDate, 
    dayname(theDate) AS theDay

    It's this last bit, getting the dayname from theDate - I get an error message saying theDate column in the field list doesn't exist. The whole code outputs an academic timetable, so I need the day of the week worked out. I've tried to do it with PHP too but I still get the same error that theDate column doesn't exist. When I take out the code to find the day name, it is fine. Can you suggest a solution or a way to work around it?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you cannot use a column alias in the WHERE clause, but you can push the entire query down one level into a subquery, and then use the alias in the outer query
    Code:
    SELECT week_number
         , DATE_FORMAT(theDate,'%Y-%m-%d') AS FormattedDate
         , DAYNAME(theDate) as Weekday
      FROM ( SELECT week_number
                  , start_date + 
                      (week_number - 
                         CASE WHEN week_number < 200
                              THEN 100 ELSE 200 END) * 7 +
                      day_of_week1 - 1 AS theDate
               FROM whatever
           ) as D
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2006
    Location
    Cardiff, Wales. UK
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers! I get what you mean and I'll give it a whirl. Thank you


Bookmarks

Posting Permissions

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