SQL 从oracle中的日期中提取月份和年份
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46095195/
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
Extract month and year from date in oracle
提问by vijaya
what is the query for extract month and year from full date. my data is like this: 1/29/2008 I tried this query:
从完整日期中提取月份和年份的查询是什么。我的数据是这样的：1/29/2008 我试过这个查询：
select ID_NO, CHECKED_DATE, to_date(TO_CHAR(CHECKED_DATE, 'MON-YYYY'), 'MON-YYYY') AS A
from Doctor_Checkup;
but It will give output: 1/1/2008
但它会给出输出：1/1/2008
Expected output: 1-2008
预期产出：1-2008
回答by William_Wilson
If the field is already a date column, you can simply cast it to the format you want:
如果该字段已经是日期列，您可以简单地将其转换为您想要的格式：
select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'mm-yyyy'),'0') AS A from Doctor_Checkup;
If it is a text column, you will need to cast to a date with format first:
如果是文本列，则需要先转换为具有格式的日期：
select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(TO_DATE(CHECKED_DATE,'dd/mm/yyyy'),'mm-yyyy'),'0') AS A from Doctor_Checkup;
回答by MT0
A date does not have a format - it is stored internally to the databaseas 7-bytes (representing year, month, day, hour, minute and second) and it is not until whatever user interface you are using (i.e. SQL/Plus, SQL Developer, Java, etc) tries to display it to you, the user, and converts it into something you would find meaningful (usually a string) that the date has a format.
日期没有格式 - 它以 7 字节（代表年、月、日、小时、分钟和秒）在内部存储到数据库中，直到您使用的任何用户界面（即 SQL/Plus、 SQL Developer、Java 等）尝试向您（用户）显示它，并将其转换为您认为有意义的内容（通常是字符串），即日期具有某种格式。
One thing to note is that a date always has the year, month, day, hour, minute and second components. Doing:
需要注意的一件事是日期始终具有年、月、日、小时、分钟和秒组成部分。正在做：
to_date(TO_CHAR(CHECKED_DATE, 'MON-YYYY'), 'MON-YYYY')
Is effectively the same as doing:
实际上与执行相同：
TRUNC( Checked_Date, 'MM' )
and will still have a day, hour, minute and second component but will have been truncated to midnight of the first day of the month. The user interface may just be have its preferences set to not display the time component (but the date will still have one).
并且仍然有天、小时、分钟和第二部分，但将被截断到当月第一天的午夜。用户界面可能只是将其首选项设置为不显示时间组件（但日期仍然会有一个）。
What you want to do is convert the date to a formatted string:
您要做的是将日期转换为格式化字符串：
select ID_NO,
CHECKED_DATE,
TRIM( LEADING '0' FROM TO_CHAR( CHECKED_DATE, 'MM-YYYY') ) AS A
from Doctor_Checkup;
or
或者
select ID_NO,
CHECKED_DATE,
EXTRACT( MONTH FROM CHECKED_DATE )
|| '-' || EXTRACT( YEAR FROM CHECKED_DATE ) AS A
from Doctor_Checkup;
回答by user7294900
To get 1-2008format use the following format with trimming leading zeroes:
要获取1-2008格式，请使用以下格式并修剪前导零：
select ID_NO,CHECKED_DATE,ltrim(TO_CHAR(CHECKED_DATE,'MM-YYYY'),'0') AS A from Doctor_Checkup;
回答by Thorsten Kettner
You want a string representing month and year in the format [M]M-YYYY. Oracle's TO_CHARonly supports MM-YYYY, though, so you'd have to get rid of a leading zero if any.
您需要一个以 [M]M-YYYY 格式表示月份和年份的字符串。TO_CHAR不过，Oracle仅支持 MM-YYYY，因此您必须去掉前导零（如果有）。
Two solutions:
两种解决方案：
trim(leading '0' from to_char(checked_date, 'mm-yyyy'))
or
或者
extract(month from checked_date) || '-' || extract(year from checked_date) from dual
回答by Peter Mayeku
SELECT ID_NO, CHECKED_DATE FROM DOCTOR_CHECKUP EXTRACT(MONTH FROM CHECKED_DATE) IN (6) AND EXTRACT(YEAR FROM CHECKED_DATE) IN (2019);
SELECT ID_NO, CHECKED_DATE FROM DOCTOR_CHECKUP EXTRACT(MONTH FROM CHECKED_DATE) IN (6) AND EXTRACT(YEAR FROM CHECKED_DATE) IN (2019);
6 MEANS THE MONTH AND 2019 IS THE YEAR
6 意味着月份和 2019 年
