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 StoreCost
FROM time_by_day t INNER JOIN
sales_fact_1997 sf7
ON t.time_id = sf7.time_id
WHERE t.the_date >='2012-01-01' AND
t.the_date <= '2012-01-07'
GROUP BY t.the_date
ORDER 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 StoreCost
FROM time_by_day t INNER JOIN
sales_fact_1997 sf7
ON t.time_id = sf7.time_id
WHERE t.the_date >= date('2012-01-01') AND
t.the_date <= date('2012-01-07')
GROUP BY t.the_date
ORDER BY t.the_date;
sam140
Updated on December 11, 2020Comments
-
sam140 over 3 years
I have a table
tine_by_day
and I know how to useTO_CHAR
function inoracle
, but how to get same output if I use inMySQL
?Is there any conversion function in
MySQL
forTO_CHAR()
?I have already tried
date_format
insteadto_char
but I'm not getting sufficient results.SELECT to_char(t.the_date,'mm-DD-YYYY') Date, SUM(sf7.unit_sales) UnitSales, SUM(sf7.store_sales) StoreSales, SUM(sf7.store_cost) StoreCost FROM time_by_day t INNER JOIN sales_fact_1997 sf7 ON t.time_id=sf7.time_id WHERE to_char(t.the_date,'YYYY-MM-DD')>='2012-01-01' AND to_char(t.the_date,'YYYY-MM-DD')<='2012-01-07' GROUP BY t.the_date ORDER BY t.the_date
-
Gordon Linoff over 9 yearsIt sort of does. The only format that always works doesn't have hyphens, but I stick with the form with hyphens. In addition to my being a human being (and finding it more readable), this form is widely accepted across databases, even in Oracle using the
date
keyword. -
sam140 over 9 yearsThanx for your suitable response but i m not using sql server .i m using only mysql
-
Maheswaran Ravisankar over 9 years@sam140 still.. only sql means? Is sql a name for DBMS?
-
Maheswaran Ravisankar over 9 yearsGot it Gordon.! Your update clarified me and the comments better n better!
-
sam140 over 9 yearsThanx for your suitable response i m using only mysql