当前位置：文江博客话题详情

如何使用 PLSQL 仅更改年份而不更改日期或月份来更新日期列？

发布于 2024-09-07 13:35:03 字数 115 浏览 8 评论 0原文

我有一个包含信用卡记录的数据库表。其中一个字段是“日期”字段。如果年份小于 2010 年，我想通过将日期的年份部分更改为 2011 来更新此字段。据我所知，PLSQL 具有时间和月份的函数，但与年份无关（据我所知）。

原文

分享到QQ

分享到微博

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

发布评论

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

绿阴红影里的.如风往事 2024-09-14 13:35:03

这展示了如何

with cc as(
select to_date('12-jan-1999') as cdate from dual union all
select to_date('12-jan-1921') as cdate from dual union all
select to_date('12-jan-1900') as cdate from dual union all
select to_date('12-jan-2000') as cdate from dual union all
select to_date('12-jan-2010') as cdate from dual
)
select  to_date( to_char(cdate,'DD-MON')  ||'-2011','DD-MON-YYYY')
from cc
where cdate < to_date('01-JAN-2010','DD-MON-YYYY')
/

This shows how to

with cc as(
select to_date('12-jan-1999') as cdate from dual union all
select to_date('12-jan-1921') as cdate from dual union all
select to_date('12-jan-1900') as cdate from dual union all
select to_date('12-jan-2000') as cdate from dual union all
select to_date('12-jan-2010') as cdate from dual
)
select  to_date( to_char(cdate,'DD-MON')  ||'-2011','DD-MON-YYYY')
from cc
where cdate < to_date('01-JAN-2010','DD-MON-YYYY')
/

回复收藏 0 原文

街角卖回忆 2024-09-14 13:35:03

1 年 = 12 个月，因此减去 12 个月：

select add_months(sysdate,-12) from dual

1 year = 12 months, so subtract 12 months:

select add_months(sysdate,-12) from dual

回复收藏 0 原文

仲春光 2024-09-14 13:35:03

以下是如何使用 add_months 来实现闰年。

with cc as( 
select to_date('12-jan-1999','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-1921','dd-mon-yyyy') as cdate from dual union all 
select to_date('29-feb-1904','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-2000','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-2010','dd-mon-yyyy') as cdate from dual 
) 
select add_months(cdate,(2011 - extract( year from cdate)) * 12)  
from cc 
where cdate < to_date('01-JAN-2010','DD-MON-YYYY');

Here's how to do it so it works with leap years using add_months.

with cc as( 
select to_date('12-jan-1999','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-1921','dd-mon-yyyy') as cdate from dual union all 
select to_date('29-feb-1904','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-2000','dd-mon-yyyy') as cdate from dual union all 
select to_date('12-jan-2010','dd-mon-yyyy') as cdate from dual 
) 
select add_months(cdate,(2011 - extract( year from cdate)) * 12)  
from cc 
where cdate < to_date('01-JAN-2010','DD-MON-YYYY');

回复收藏 0 原文

~没有更多了~