日期格式化sql plus

发布于 2024-10-28 11:32:52 字数 340 浏览 2 评论 0原文

我有这个sql脚本：

select 'LASTBUSDATE='|| to_char(max(calen_dt),'mmdd') LBD
from put_calen
where calen_dt < (select calen_dt from put_calen where ca_run_dt_ind = 'Y')
and business_day_ind = 'Y';
exit;

如何修改它，以便我可以获得上个月的第一个工作日和最后一个工作日，例如2-1-2011和2-28-2011

请帮忙。不知道这里发生了什么。

原文

I have this sql script:

select 'LASTBUSDATE='|| to_char(max(calen_dt),'mmdd') LBD
from put_calen
where calen_dt < (select calen_dt from put_calen where ca_run_dt_ind = 'Y')
and business_day_ind = 'Y';
exit;

How can I modify this so I will get first business day and last business day of previous month like 2-1-2011 and 2-28-2011

Please help. Don't know whats going on in here.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

ぃ弥猫深巷。 2024-11-04 11:32:52

我从这里的列名称猜测您的表结构，但是以下内容是否返回您想要的内容？

select min(calen_dt) as first_business_day
      ,max(calen_dt) as last_business_day
  from put_calen
 where calen_dt >= trunc(sysdate,'MM') - interval '1' month
   and calen_dt <= trunc(sysdate,'MM') - interval '1' day
   and business_day_ind = 'Y';

如果 sysdate = "2011-03-31 19:14:32"，则 trunc(sysdate,'MM') 将返回 "2011-03-01 00:00:00"；基本上日期被截断为月份。

您想要上个月的第一个和最后一个工作日，因此我减去 1 个月以得出上个月的第一天，并减去 1 天以得出上个月的最后一天。过滤器 business_day_ind = 'Y' 确保仅考虑工作日作为最小/最大。

让我知道它是如何工作的，或者如果我误解了你的问题和/或表结构:)

I'm guessing on your table structure from your column names here, but does the following return what you want?

select min(calen_dt) as first_business_day
      ,max(calen_dt) as last_business_day
  from put_calen
 where calen_dt >= trunc(sysdate,'MM') - interval '1' month
   and calen_dt <= trunc(sysdate,'MM') - interval '1' day
   and business_day_ind = 'Y';

If sysdate = "2011-03-31 19:14:32", then trunc(sysdate,'MM') would return "2011-03-01 00:00:00"; basically the date truncated to month.

You wanted the first and last business day in previous month, so I subtracted 1 month to arrive at the first of the previous month, and 1 day to arrive at the last day of the previous month. The filter business_day_ind = 'Y' makes sure that only business days are consider for the min/max.

Let me know how it works, or if I missunderstood your question and/or table structure :)

回复收藏 0 原文

~没有更多了~