I'm trying to change the year of all of the birthdates to 2005 like the SEND CARD column.

I have three rows:

I can truncate it to have the SEND CARD be

01-JUL-1980 
01-MAR-1979 
01-MAR-1969 

Which is what I want, but I need to year to be 2005. How do I change just the year?

SELECT first_name, last_name, birthdate, TRUNC(birthdate, 'Month')
  FROM f_staffs;

CodePudding user response：

You don't have to complicate it that much; a simpler option is

TO_CHAR (birthdate, 'fmMonth') || ' 2005' AS send_card

Sample data:

SQL> WITH
  2     f_staffs (first_name, birthdate)
  3     AS
  4        (SELECT 'Sue', DATE '1980-07-01' FROM DUAL
  5         UNION ALL
  6         SELECT 'Bob', DATE '1979-03-19' FROM DUAL
  7         UNION ALL
  8         SELECT 'Monique', DATE '1969-03-30' FROM DUAL)

Query:

  9  SELECT first_name,
 10         birthdate,
 11         TO_CHAR (birthdate, 'fmMonth') || ' 2005' AS send_card
 12    FROM f_staffs;

FIRST_N BIRTHDATE   SEND_CARD
------- ----------- --------------
Sue     01-jul-1980 July 2005
Bob     19-mar-1979 March 2005
Monique 30-mar-1969 March 2005

SQL>

CodePudding user response：

Here's the answer in case anyone asks in the future!

SELECT first_name, last_name, To_CHAR(birthdate, 'DD-MON-YYYY') AS "BIRTHDATE", TO_CHAR(TRUNC(ADD_MONTHS(birthdate, ((2005 - EXTRACT(YEAR FROM birthdate)) * 12)), 'Month'), 'Month YYYY') AS "SEND CARD"
FROM f_staffs;