Oracle SQL从零件创建日期 [英] Oracle SQL Create Date from Parts

问题描述

我在名为"EFFECTIVE_DATE"和"POINT_DATE"的数据库中有两个日期字段

I have two date fields in a database called "EFFECTIVE_DATE" and "POINT_DATE"

如何创建一个新的日期字段，其中的日期由"EFFECTIVE_DATE"的年份，"POINT_DATE"的月份和"POINT_DATE"的日期组成?

How do I create a new date field, where the date is made up from the year of "EFFECTIVE_DATE", the month of "POINT_DATE" and the day of "POINT_DATE" ?

我通常会使用Datefromparts，但这是Oracle数据库而不是Microsoft

I would normally use Datefromparts, but this is an Oracle Database not Microsoft

亲切的问候

推荐答案

以下是使用ADD_MONTHS和EXTRACT(YEAR FROM ....)的方法.您只需添加或减去所需的月数(由于只更改年份，所以总是12的倍数).与TO_CHAR/TO_DATE解决方案不同，此方法可处理leap日(2月29日).另一方面，请注意，将日期从2003年2月28日更改为2012年会将其更改为2月29日(ADD_MONTHS将一个月的最后一天更改为结果月份的最后一天).

Here's an approach using ADD_MONTHS and EXTRACT(YEAR FROM ....). You simply add or subtract the needed number of months (always a multiple of 12, since you are only changing the year). Unlike the TO_CHAR / TO_DATE solutions, this approach handles leap days (Feb. 29). On the other hand, be advised that changing the date from 28 Feb. 2003 to the year 2012 will change it to Feb. 29 (ADD_MONTHS changes the last day of a month to the last day of the resulting month).

with
     inputs as (
       select date '2013-03-22' as effective_date,
              date '2017-08-14' as point_date
       from   dual
     )
-- end of TEST data (do not include in the solution!)
select effective_date, point_date,
       add_months(point_date, 12 * ( extract (year from effective_date) - 
                                     extract (year from point_date)     )
                 ) as mixed_date
from   inputs;

EFFECTIVE_DATE  POINT_DATE  MIXED_DATE
--------------  ----------  ----------
03/22/2013      08/14/2017  08/14/2013

这篇关于Oracle SQL从零件创建日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！