转换月名到月号SQL

Question

在ORACLE SQL中是否有一个函数可以将月份名称转换为月份数字，但只是没有日期和年份?例如，我有FEB，我想转换它在02

Answer 1

您可以使用CASE表达式:

SELECT CASE month_name
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
ELSE NULL
END AS month_number
FROM   your_table;

或DECODE函数:

SELECT DECODE(
month_name,
'JAN', 1,
'FEB', 2,
'MAR', 3,
'APR', 4,
'MAY', 5,
'JUN', 6,
'JUL', 7,
'AUG', 8,
'SEP', 9,
'OCT', 10,
'NOV', 11,
'DEC', 12
) AS month_number
FROM   your_table;

或TO_CHAR和TO_DATE:

SELECT TO_CHAR(
TO_DATE(month_name, 'MON', 'NLS_DATE_LANGUAGE=American'),
'MM'
) AS month_number
FROM   your_table;

orEXTRACTandTO_DATE:

SELECT EXTRACT(
MONTH FROM TO_DATE(month_name, 'MON', 'NLS_DATE_LANGUAGE=American')
) AS month_number
FROM   your_table;