How to use to_char function functionality in MySQL
In SQL Server, you would typically use the convert() function, which is not nearly as convenient as to_char(). For your query, you only need it in the select clause:
SELECT convert(varchar(10), t.the_date, 110) as Date, SUM(sf7.unit_sales) as UnitSales, SUM(sf7.store_sales) as StoreSales, SUM(sf7.store_cost) as StoreCostFROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id = sf7.time_idWHERE t.the_date >='2012-01-01' AND t.the_date <= '2012-01-07'GROUP BY t.the_dateORDER BY t.the_date;SQL Server will normally treat the ISO standard YYYY-MM-DD as a date and do the conversion automatically. There is a particular internationalization setting that treats this as YYYY-DD-MM, alas. The following should be interpreted correctly, regardless of such settings (although I would use the above form):
WHERE t.the_date >= cast('20120101' as date) AND t.the_date <= cast('20120107' as date)EDIT:
In MySQL, you would just use date_format():
SELECT date_format(t.the_date, '%m-%d-%Y') as Date, SUM(sf7.unit_sales) as UnitSales, SUM(sf7.store_sales) as StoreSales, SUM(sf7.store_cost) as StoreCostFROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id = sf7.time_idWHERE t.the_date >= date('2012-01-01') AND t.the_date <= date('2012-01-07')GROUP BY t.the_dateORDER BY t.the_date;
Based on Gordons approach, but usign CHAR(10) instead of VARCHAR(10) since there's hardly a date not being returned with a length of 10...
SELECT convert(char(10), t.the_date, 110) as [Date], SUM(sf7.unit_sales) as UnitSales, SUM(sf7.store_sales) as StoreSales, SUM(sf7.store_cost) as StoreCostFROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id = sf7.time_idWHERE t. the_date >='20120101' AND t.the_date <= '20120107'GROUP BY t.the_dateORDER BY t.the_date;Edit: also changed the date format in the WHERE clause to be ISO compliant and therewith not affected by the setting of DATEFORMAT.