Stories
Slash Boxes
Comments

NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

use Perl

All the Perl that's Practical to Extract and Report

Calculating years (anniversaries) between dates in SQL More | Login | Reply

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Without JavaScript enabled, you might want to use the classic discussion system instead. If you login, you can remember this preference.

Try this... (Score:2)

by bart (450)

I don't have access to an Oracle system right now, but this might just work.
years := trunc((to_number(to_char(date2, 'YYYYMMDD'))-to_number(to_char(date1, 'YYYYMMDD')))/10000)

The idea is that you just want to subtract the years, but when the value for 'MMDD' for the lower date (date1), taken as a 4 digit number, is larger than that of the later (date2), then you'll have a "borrow" (AKA "carry" for subtraction) from the years. And then, you'll just throw the days/months value away, as it's incorrect anywa
- Re:Try this... (Score:1)
  
  by grinder (1100) on 2007.04.02 3:53 (#54138) Homepage Journal
  
  ooh sweet, I like this. The yyyymmdd insight is very clever. According to some quick tests it checks out just fine.
  
  Thanks!
  
  Reply to This
  
  Parent

Get More Comments

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.