在 (DB2) SQL 中选择上个月的第一天
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1063174/
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
Select first day of preceding month in (DB2) SQL
提问by nearly_lunchtime
I need to check whether a given date is in the preceding month for a monthly query. I can get
我需要检查给定日期是否在上个月进行每月查询。我可以得到
CURRENT DATE - 1 MONTH
to compare the selected date with, but I can't assume the current date will be the first day of each month exactly. Is there a built in way to get the first day of the month without extracting the year and month and gluing it back together?
将所选日期与，但我不能假设当前日期将是每个月的第一天。是否有一种内置方法可以在不提取年份和月份并将其重新粘在一起的情况下获取月份的第一天？
回答by
First Day of the Current Month:
当月的第一天：
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS
First of the Current Year is
今年的第一天是
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - (MONTH(CURRENT_DATE)-1) MONTHS
Last Day of the Last Month:
上个月的最后一天：
CURRENT_DATE - DAY(CURRENT_DATE) DAYS
First Day of the Last Month:
上个月的第一天：
CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAYS - 1 MONTH
回答by Fred Sobotka
First day of this year:
今年的第一天：
date('0001-01-01') + year(current date) years - 1 year
First day of this month:
本月第一天：
date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 1 month
First day of last month:
上个月的第一天：
date('0001-01-01') + year(current date) years - 1 year + month(current date) months - 2 months
回答by Nolan Roberts
Here are snippets from my DB2 SQL Month Calculation Cheat Sheet:
以下是我的 DB2 SQL Month Calculation Cheat Sheet 中的片段：
Beginning of Current Month:
当月的开始：
CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS
End of Current Month:
当月末：
LAST_DAY(CURRENT DATE)
Beginning of Prior Month:
上个月的开始：
CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS - 1 MONTHS
End of Prior Month:
上月末：
LAST_DAY(CURRENT DATE - 1 MONTHS)
Beginning of Next Month:
下月初：
CURRENT DATE + 1 DAYS - DAY(CURRENT DATE) DAYS + 1 MONTHS
End of Next Month:
下月底：
LAST_DAY(CURRENT DATE + 1 MONTHS)
回答by Greg
LUW 9.7 (and I think z/os) methods using built-in functions.
LUW 9.7（我认为是 z/os）使用内置函数的方法。
Last day of Current Month:
当月的最后一天：
LAST_DAY(CURRENT DATE)
First day of Previous Month:
上个月的第一天：
LAST_DAY(CURRENT DATE - 2 MONTHS) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE - 1 month, 'MONTH')
First day of Current Month:
当月的第一天：
LAST_DAY(CURRENT DATE - 1 MONTH) + 1 DAY or (LUW) TRUNCATE(CURRENT DATE, 'MONTH');
回答by MadMurf
In order to flesh this out with more info. The solution above is perfect if you want the First day of the month, say you want the Last day of the month, or in my case I want the last day of the next quarter.
为了用更多信息充实这一点。如果您想要本月的第一天，例如您想要本月的最后一天，或者在我的情况下我想要下一个季度的最后一天，则上述解决方案是完美的。
From above I was doing
从上面我在做
date('0001-01-31') + year(date(prevQdate))years - 1 year + month(prevQdate) months + 2 months)
Which wasn't giving me what I wanted, sometimes the date was the 30th instead of 31st for months with 31 days...
这并没有给我我想要的东西，有时日期是 30 号而不是 31 号，几个月有 31 天......
Changing it to
将其更改为
date('0001-01-31') + year(date(prevQdate))years - 1 year + **(month(prevQdate) + 2)** months)
Gave me what I wanted. Looks like the first addition of the Months from the prev quarter date was resetting the DAY part of my date and thus the second addition of months was working on a date with only 30 days in the month.
给了我我想要的。看起来从上一季度日期开始添加的第一个月份是重置我日期的 DAY 部分，因此第二个月份的添加是在该月只有 30 天的日期上工作。
Just something to be wary of when using this method of date manipulation in DB2.
在 DB2 中使用这种日期操作方法时需要注意的事项。
回答by moleboy
I believe the following will work for you.
我相信以下对你有用。
ROUND_TIMESTAMP (somedate,'W')
