-1

I do have a requirement like I need to calculate a person age with sysdate. Calculating the persons age should be exactly greater or lesser than 75years and 09 months with the sysdate. but at some scnearios its not working out. can any one of help with this issues.

IF TO_CHAR(bday,'mm/dd')='02/29' THEN
age = ADD_MONTHS(bday-1,12*75);
ELSE
age = ADD_MONTHS(bday,12*75);
END IF;

but this is not working at some conditions. could anyone help with this requirement

Jesuraja
  • 3,774
  • 4
  • 24
  • 48
user3450443
  • 9
  • 2
  • 4
    You have the [tag:mysql] and [tag:sql-server] tags, but the `sysdate` reference suggests Oracle. So which one is it? What RDBMS are you using? – Mureinik Jun 09 '14 at 05:49
  • Check this thread [enter link description here][1] [1]: http://stackoverflow.com/questions/288984/the-difference-in-months-between-dates-in-mysql – Abubakr Elkabany Jun 09 '14 at 05:55

3 Answers3

1

Try this:

Find Years: Find total days between Current date and DOB and divide it by 365.

Find Months: Find remaining days and divide it by 30.

DECLARE @DOB DATETIME
DECLARE @Days INT  
DECLARE @Year INT
DECLARE @Month INT
DECLARE @DaysRemaining INT

SET @DOB ='9/4/1938'
SET @Days = DATEDIFF(DAY,@DOB,GETDATE())
SET @Year = @Days / 365
SET @DaysRemaining = @Days - (365 * @Year)
SET @Month = @DaysRemaining / 30

SELECT @DOB AS DOB, CAST(@Year AS VARCHAR) + ' years ' + CAST(@Month AS VARCHAR) + ' months' AS Age

Try using this logic and apply your condition.

Jesuraja
  • 3,774
  • 4
  • 24
  • 48
0

DATEDIFF--Subtract two dates will help you.

Please check example here:--

http://answers.oreilly.com/topic/163-how-to-calculate-the-interval-between-two-dates-or-times-in-mysql/

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
0
DECLARE @DOB DATETIME
DECLARE @RequiredAgeInMonths INT  
DECLARE @ActualAgeInMonths INT

SET @DOB ='9/4/1938'
SET @RequiredAgeInMonths=909
SET @ActualAgeInMonths = (SELECT DATEDIFF(MM,@DOB,GETDATE()))

IF(@ActualAgeInMonths = @RequiredAgeInMonths)
PRINT 'YOUR AGE IS 75 YEARS AND 9 MONTHS'

ELSE 
 PRINT 'YOUR AGE IS NOT EQUAL TO 75 YEARS AND 9 MONTHS'
prasad
  • 187
  • 2
  • 9