• Post Reply Bookmark Topic Watch Topic
  • New Topic

SQL Question  RSS feed

 
Fred Victa
Ranch Hand
Posts: 200
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is there a way to create a SQL query that can find the current system date minus one year taking a leap year into account?
 
Joe Jaber
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT DATE_SUB( CURDATE(),INTERVAL 1 YEAR);

DATE_SUB  for subtract date
CUR_DATE  get current date
 
Dave Tolls
Ranch Hand
Posts: 2410
25
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Depends on the database, but they usually have an equivalent to the DATE_SUB that Joe mentions.
 
Paul Clapham
Sheriff
Posts: 22099
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And as for the leap-year issue (yes, that could be an important issue which needs to be addressed) -- you would have to read the database's documentation for that function. It should explain what it does with Feb 28/29 and that may vary between databases.
 
Fred Victa
Ranch Hand
Posts: 200
1
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
These examples are for Oracle SQL:

--Subtract one year from current date while taking a leap year into account.
SELECT ADD_MONTHS(SYSDATE,-12) FROM DUAL;

--returns 31-MAR-07
select add_months('28-FEB-2007', 1) from dual;

--returns 28-MAR-08
select add_months('28-FEB-2008', 1) from dual;
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!