如何在 MySQL 中使用 to_char 函数功能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25241574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to use to_char function functionality in MySQL
提问by sam140
I have a table tine_by_dayand I know how to use TO_CHARfunction in oracle, but how to get same output if I use in MySQL?
我有一张表tine_by_day，我知道如何使用TO_CHAR函数 in oracle，但是如果使用 in ，如何获得相同的输出MySQL？
Is there any conversion function in MySQLfor TO_CHAR()?
MySQLfor 中有没有转换函数TO_CHAR()？
I have already tried date_formatinstead to_charbut I'm not getting sufficient results.
我已经尝试date_format代替to_char，但我没有得到令人满意的结果。
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
回答by Gordon Linoff
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 selectclause:
在 SQL Server 中，您通常会使用该convert()函数，但它不如to_char(). 对于您的查询，您只需要在select子句中使用它：
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 normallytreat 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):
SQL Server通常会将ISO 标准 YYYY-MM-DD 视为日期并自动进行转换。有一个特殊的国际化设置将其视为 YYYY-DD-MM，唉。无论此类设置如何，以下内容都应正确解释（尽管我会使用上述形式）：
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():
在 MySQL 中，您只需使用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;
回答by Lmu92
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...
基于 Gordons 方法，但使用 CHAR(10) 而不是 VARCHAR(10) 因为几乎没有一个日期不返回长度为 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 StoreCost
FROM time_by_day t INNER JOIN
sales_fact_1997 sf7
ON t.time_id = sf7.time_id
WHERE t. the_date >='20120101' AND
t.the_date <= '20120107'
GROUP BY t.the_date
ORDER 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.
编辑：还将 WHERE 子句中的日期格式更改为符合 ISO，因此不受 DATEFORMAT 设置的影响。
回答by SkylerLin
Recommend you do this :
建议你这样做：
CAST($(STR) AS CHAR(40))
