SQL 如何将日期转换为月份数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14041200/
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 convert date into month number?
提问by Taoqir
I have a column Monthin my table. The month name and date are stored in this month column like
我的表中有一个Month列。月份名称和日期存储在本月列中，例如
Month
01-JAN-12
02-FEB-12
and so on.
等等。
How do I convertthe DATEinto month number such as
如何转换的日期为本月数如
Month
1
2
etc.
等等。
回答by Mari
select
to_char(to_date('01-JAN-12','dd-mon-yy'),'mm') from dual;
回答by Harrison
Extractworks perfectly for this
提取物非常适合这个
EXTRACT extracts and returns the value of a specified datetime field
EXTRACT 提取并返回指定日期时间字段的值
with fullYear as(
select (to_date('01-jan-12') + 29*level) dte
from dual
connect by level <= 12
)
select extract(month from dte) month, dte from fullYear ;
gives you
给你
MONTH DTE
1 January, 30 2012 00:00:00+0000
2 February, 28 2012 00:00:00+0000
3 March, 28 2012 00:00:00+0000
4 April, 26 2012 00:00:00+0000
5 May, 25 2012 00:00:00+0000
6 June, 23 2012 00:00:00+0000
7 July, 22 2012 00:00:00+0000
8 August, 20 2012 00:00:00+0000
9 September, 18 2012 00:00:00+0000
10 October, 17 2012 00:00:00+0000
11 November, 15 2012 00:00:00+0000
12 December, 14 2012 00:00:00+0000
回答by kmkaplan
Use TO_CHARfunction as in TO_CHAR(my_column, 'MM').
使用 TO_CHAR功能如TO_CHAR(my_column, 'MM')。
For your specific format you would need the format converter TO_CHAR(month, 'FmMM')(thanks to Nicholas Krasnov for this trick).
对于您的特定格式，您需要格式转换器TO_CHAR(month, 'FmMM')（感谢 Nicholas Krasnov 的这个技巧）。
If your monthcolumn is not already of a date type you will first need to convert it to a date: TO_CHAR(TO_DATE(month, 'DD-Mon-IY'), 'FmMM').
如果您的month列还不是日期类型，您首先需要将其转换为日期：TO_CHAR(TO_DATE(month, 'DD-Mon-IY'), 'FmMM')。
回答by Art
Totally agree regarding writing a case when Oracle offers built-in functionality:
完全同意在 Oracle 提供内置功能时编写案例：
SELECT EXTRACT(MONTH FROM DATE '2012-03-15') FROM DUAL;
SELECT EXTRACT(MONTH FROM TO_DATE('01-JAN-12', 'DD-MON-RR')) month_to_number FROM DUAL;
select to_number(to_char(to_date('01-JAN-12','dd-mon-yy'),'mm')) month_to_number from dual;
select to_number(to_char(trunc(sysdate), 'mm')) month_to_number from dual;
回答by Mimi
datepart function do this easily
datepart 函数很容易做到这一点
SELECT DATEPART(m, getdate())
回答by fred
You can use the "DatePart" function too :
您也可以使用“DatePart”功能：
http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/dml/funcs/datepart.htm
http://docs.oracle.com/cd/E10530_01/doc/epm.931/html_esb_techref/maxl/dml/funcs/datepart.htm
回答by zafus_coder
SELECT ( CASE yourDate
WHEN 'Jan' THEN 1
WHEN 'Feb' THEN 2
WHEN 'Mar' THEN 3
WHEN 'Apr' THEN 4
WHEN 'May' THEN 5
WHEN 'Jun' THEN 6
WHEN 'Jul' THEN 7
WHEN 'Aug' THEN 8
WHEN 'Sep' THEN 9
WHEN 'Oct' THEN 10
WHEN 'Nov' THEN 11
WHEN 'Dec' THEN 12
END )
