Oracle SQL Create Date from Parts

Question

I have two date fields in a database called "EFFECTIVE_DATE" and "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" ?

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

Kind Regards

There may be a more elegant way but TO_CHAR() and TO_DATE() should work. You can also use EXTRACT(). The hard part is ensuring you don't end up with invalid dates, e.g. Feb 31. — Álvaro González
– Álvaro González, Commented Mar 13, 2017 at 17:16

user5683823 · Accepted Answer · 2017-03-13 18:06:30Z

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

Gordon Linoff · Accepted Answer · 2017-03-13 17:17:51Z

1

Hmmm . . . this produces a nice string in the YYYY-MM-DD format:

select to_char(effective_date, 'YYYY') || '-' || to_char(point_date, 'MM-DD')

And this parses it back to a date:

select to_date(to_char(effective_date, 'YYYY') || '-' || to_char(point_date, 'MM-DD'), 'YYYY-MM-DD')

Note: You might want to be careful about Feb 29th.

answered Mar 13, 2017 at 17:17

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

3 Comments

unleashed Over a year ago

And be careful of dates like June 31st.

Gordon Linoff Over a year ago

@unleashed . . . I don't see how this would product June 31st. I do see how it would produce 2017-02-29.

unleashed Over a year ago

My bad. At the time, I was thinking it was taking just the day from the second date. If it takes a valid day AND month, then this is fine except for 2/29 scenarios.

Wernfried Domscheit · Accepted Answer · 2017-03-13 17:17:31Z

0

Assuming all coulmns are DATE data types, you can use this one

TO_DATE(TO_CHAR(EFFECTIVE_DATE, 'YYYY') || TO_CHAR(POINT_DATE, 'MMDD'), 'YYYYMMDD')

Be aware of leap years!

answered Mar 13, 2017 at 17:17

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

Oracle SQL Create Date from Parts

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related