从MM-DD-YYYY格式sql中获取月

I'm trying to get only the month from my date column and I can't seem to get it using my query.

我试着从我的日期列中只得到一个月，而且我似乎不能用我的查询得到它。

The DATE is my column field and I only want to display the month. Can any of you help me on this?

日期是我的列字段，我只想显示月份。你们谁能帮我一下吗?

Query:

查询:

SELECT MONTH(CONVERT(VARCHAR(8), GETDATE(DATE), 1)) FROM adjtime WHERE EMPLOYEE = 302

4 个解决方案

#1

If the DATE is in varchar, you need to convert as DATE format using

如果日期在varchar中，则需要使用日期格式进行转换

STR_TO_DATE(`DATE`, '%m/%d/%Y')

Sample execution with sample data:

样本执行与样本数据:

CREATE TABLE `adjtime` (EMPLOYEE int, `DATE` VARCHAR (20) );

INSERT INTO `adjtime` (EMPLOYEE , `DATE`) 
SELECT 302, '1/1/2017' UNION
SELECT 302, '11/22/2017' UNION
SELECT 302, '2/20/2017' UNION
SELECT 301, '3/6/2017';

SELECT MONTH(STR_TO_DATE(`DATE`, '%m/%d/%Y')) AS `MonthNumber` 
FROM `adjtime` 
WHERE `EMPLOYEE` = 302;

或

SELECT MONTHNAME(STR_TO_DATE(`DATE`, '%m/%d/%Y')) AS `MonthName` 
FROM `adjtime` 
WHERE `EMPLOYEE` = 302;

Rextester Demo: http://rextester.com/GFOTO88036

Rextester演示:http://rextester.com/GFOTO88036

4 个解决方案

#1

更多相关文章

随机推荐