SQL 一个月的天数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13766611/
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
Number of days in a month
提问by Raj More
I have a monthly amount that I need to spread equally over the number of days in the month. The data looks like this:
我有一个每月的金额，我需要在当月的天数上平均分配。数据如下所示：
Month Value
----------- ---------------
01-Jan-2012 100000
01-Feb-2012 121002
01-Mar-2012 123123
01-Apr-2012 118239
I have to spread the Jan amount over 31 days, the Feb amount over 29 days and the March amount over 31 days.
我必须将 1 月的金额分摊超过 31 天，将 2 月的金额分摊超过 29 天，并将 3 月的金额分摊超过 31 天。
How can I use PL/SQL to find out how many days there are in the month given in the month column?
如何使用 PL/SQL 找出月份列中给出的月份中有多少天？
采纳答案by UniversE
Don't use to_char()and stuff when doing arithmetics with dates.
Strings are strings and dates are dates. Please respect the data types and use this instead:
to_char()在对日期进行算术运算时不要使用and 。字符串是字符串，日期是日期。请尊重数据类型并使用它：
1+trunc(last_day(date_column))-trunc(date_column,'MM')
1+trunc(last_day(date_column))-trunc(date_column,'MM')
Indeed, this is correct. It computes the difference between the valueof the last day of the month and the valueof the first (which is obviously always 1 and therefore we need to add this 1 again).
确实，这是正确的。它计算该月最后一天的值与第一天的值之间的差值（显然始终为 1，因此我们需要再次添加此 1）。
You must not forget to use the trunc()function if your date columns contains time, because last_day()preserves the time component.
trunc()如果您的日期列包含时间，您一定不要忘记使用该函数，因为last_day()保留了时间组件。
回答by a1ex07
SELECT CAST(to_char(LAST_DAY(date_column),'dd') AS INT)
FROM table1
回答by Art
SELECT EXTRACT(DAY FROM LAST_DAY(SYSDATE)) num_of_days FROM dual;
/
SELECT SYSDATE, TO_CHAR(LAST_DAY(SYSDATE), 'DD') num_of_days FROM dual
/
-- Days left in a month --
SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days left"
FROM DUAL
/
回答by Justin Cave
You can add a month and subtract the two dates
您可以添加一个月并减去两个日期
SQL> ed
Wrote file afiedt.buf
1 with x as (
2 select date '2012-01-01' dt from dual union all
3 select date '2012-02-01' from dual union all
4 select date '2012-03-01' from dual union all
5 select date '2012-01-31' from dual
6 )
7 select dt, add_months(trunc(dt,'MM'),1) - trunc(dt,'MM')
8* from x
SQL> /
DT ADD_MONTHS(TRUNC(DT,'MM'),1)-TRUNC(DT,'MM')
--------- -------------------------------------------
01-JAN-12 31
01-FEB-12 29
01-MAR-12 31
31-JAN-12 31
回答by koljaTM
select add_months(my_date, 1)-my_date from dual;
回答by SAH
Use the following Oracle query:
使用以下 Oracle 查询：
select to_number(to_char(last_day(sysdate),'dd')) TotalDays from dual
Date_Parameter='01-Oct-2017'
select to_number(to_char(last_day('Date_Parameter'),'dd')) TotalDays from dual
回答by Pranav Shah
SELECT TO_CHAR(LAST_DAY(SYSDATE), 'fmday-mon-rr dd') as LastDayOfMonth
FROM dual;
