SQL 如何在postgres中获得一个月的最后一天？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28186014/
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 get the last day of month in postgres?
提问by Hare Rama Hare Krishna
How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using
如何在postgres中找到本月的最后一天？我有一个日期列以格式（YYYYMMDD）存储为数字（18）我正在尝试使用它使其成为日期
to_date("act_dt",'YYYYMMDD') AS "act date"
then find the last day of this date: like this:
然后找到这个日期的最后一天：像这样：
(select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date)
but it gives me this error:
但它给了我这个错误：
ERROR: Interval values with month or year parts are not supported
Detail:
-----------------------------------------------
error: Interval values with month or year parts are not supported
code: 8001
context: interval months: "1"
query: 673376
location: cg_constmanager.cpp:145
process: padbmaster [pid=20937]
-----------------------------------------------
Any help?
有什么帮助吗？
Postgres version:
Postgres 版本：
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874
采纳答案by Houari
回答by wspurgin
For anybody coming to this question looking for the Postgresway to do this (not using Redshift), here's how you'd do it:
对于任何人来到这个问题寻找Postgres方式来做到这一点（不使用 Redshift），这是你的方法：
SELECT (date_trunc('month', '2017-01-05'::date) + interval '1 month' - interval '1 day')::date
AS end_of_month;
Replacing the '2017-01-05'with whatever date you want to use. You can make this into a function like this:
'2017-01-05'用您想要使用的任何日期替换。你可以把它变成这样的函数：
create function end_of_month(date)
returns date as
$$
select (date_trunc('month', ) + interval '1 month' - interval '1 day')::date;
$$ language 'sql'
immutable strict;
回答by Andomar
Okay, so you've got a numeric(18)column containing numbers like 20150118. You can convert that to a date like:
好的，所以你有一numeric(18)列包含像20150118. 您可以将其转换为日期，例如：
to_date(your_date_column::text, 'YYYYMMDD')
From a date, you can grab the last day of the monthlike:
从某个日期开始，您可以获取该月的最后一天，例如：
(date_trunc('month', your_date_column) +
interval '1 month' - interval '1 day')::date;
Combined, you'd get:
结合起来，你会得到：
select (date_trunc('month', to_date(act_dt::text, 'YYYYMMDD')) +
interval '1 month' - interval '1 day')::date
from YourTable;
回答by Daniel Evanko
For future searches, Redshift does not accept INTERVAL '1 month'. Instead use dateadd(month, 1, date)as documented here.
对于未来的搜索，Redshift 不接受INTERVAL '1 month'. 而是dateadd(month, 1, date)按照此处记录的方式使用。
To get the end of the month use: DATEADD(DAY, -1, (DATE_TRUNC('month', DATEADD(MONTH, 1, date))))
要获得月底，请使用： DATEADD(DAY, -1, (DATE_TRUNC('month', DATEADD(MONTH, 1, date))))
回答by Rakesh H B
select to_char(date_trunc('month', now() + '01 Months'::interval) - '01 Days'::interval, 'YYYYmmDD'::text)::numeric as end_period_n
选择 to_char(date_trunc('month', now() + '01 Months'::interval) - '01 Days'::interval, 'YYYYmmDD'::text)::numeric as end_period_n
