Look, the only thing that is certain from the SQL2005 books online is that it stores the date data as a 4-byte number. So anything that shows you the date in any recognisable date format is formatting the integer into a date. What enterprise manager does and what queries return are governed by the date formatting in place.
The error in your code is that you forgot the style-indicator (I use 106) in the CONVERT()
$query = "SELECT CONVERT(CHAR(8),class_date, 106), start_datetime, end_datetime ".
"FROM class ".
"WHERE course_ID = '$course' ".
"AND location_ID = '$location' ".
"ORDER BY class_date DESC";
That will return 09/02/2006.
Typical of mickeysoft, most of the defined date formats are not what I want for display anyway, so doing it in the presentation code is where we are at again.
The default display format in transact sql is aparently Mon dd, yyyy hh:mmAM/PM and when no time is present it defaults to 12:00AM.
Fat lot of use the 12:00AM is if you have not stored a time then why would you want a default time?????
Most of the others are 2 digit years ???? After Y2K, why???
I just wish everyone would do the sensible thing and use the ISO 8601 datetime format yyyy-mm-dd hh:mm:ss Self-sorting, language independent, and available range from the year dot to 9999-12-31. I'm sure we'll have other requirements by the year 10,000 AD.