Oracle SQL 获取每月的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39031789/
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
Oracle SQL to get first day of month
提问by Soumyajeet Padhy
Requirement is input date should produce first day of the month. Condtions are:
要求是输入日期应产生当月的第一天。条件是：
- If the date entered is between 16-nov to 30-nov, then the first day will be 16-nov.
- if the date entered is between 1-nov to 15-nov , then the first day will be 01-nov.
- for all other month it should return 01st day of corresponding month.
- 如果输入的日期在 11 月 16 日到 11 月 30 日之间，则第一天将是 11 月 16 日。
- 如果输入的日期介于 1-nov 到 15-nov 之间，则第一天将是 01-nov。
- 对于所有其他月份，它应该返回相应月份的第 1 天。
回答by Codo
Building on Tim Biegeleisen's solution, simplifying it and avoid the date-to-text-to-date conversions. Note the use of TRUNCto get the first date of the period.
以 Tim Biegeleisen 的解决方案为基础，对其进行简化并避免日期到文本到日期的转换。请注意使用TRUNC来获取期间的第一个日期。
SELECT
CASE
WHEN EXTRACT(MONTH FROM DATE_COL) = 11 AND EXTRACT(DAY FROM DATE_COL) >= 16
THEN TRUNC(DATE_COL, 'MONTH') + 15
ELSE TRUNC(DATE_COL, 'MONTH')
END AS FIRST_OF_MONTH
FROM T1
回答by Tim Biegeleisen
I used a lengthy CASEexpression to handle this, containing the logic for the three cases you mentioned in your question.
我使用了一个冗长的CASE表达式来处理这个问题，其中包含您在问题中提到的三种情况的逻辑。
SELECT CASE WHEN EXTRACT(month FROM date) = 11 AND
EXTRACT(day FROM date) >= 16
THEN TO_DATE(EXTRACT(year FROM date) || '-11-16', 'yyyy-mm-dd')
ELSE TO_DATE(EXTRACT(year FROM date) || '-' || EXTRACT(month FROM date) ||
'-01', 'yyyy-mm-dd')
END AS newDate
FROM yourTable
