CODEWITHSUNDEEP
sqloracle-databasedatetimedate-arithmetic
jake
jake

Reputation: 1445

How do update a date column by changing only the year and not the day or month using PLSQL?

I have a database table containing credit card records. One of the fields is a Date field. I would like to update this field by changing the year portion of the date to 2011 if the year is less than 2010. From what i have found, PLSQL has functions for time and months but nothing to do with years (to my knowledge).

Upvotes: 2

Views: 6606

Answers (3)

Daniel Emge
Daniel Emge

Reputation: 1617

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');

Upvotes: 1

Pop
Pop

Reputation: 4022

1 year = 12 months, so subtract 12 months:

select add_months(sysdate,-12) from dual

Upvotes: 3

josephj1989
josephj1989

Reputation: 9709

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')
/

Upvotes: 5

Related Questions