Get past date before days / month / years using date functions in sql

Question

I am trying to calculate birth date from year,month and day input from user.

Below example will give some idea of what exactly i am trying to achieve.

Basically there are two steps

1 ) Get days/months and years from date of birth (Date entered from user). for example 04 Feb 1988 should return 29 years 8 months 28 days

2 ) Get Date from entered days/months and years. for example 29 years 8 months and 28 days should give 04 Feb 1988 back

Step 1 is working fine but with case 2 i am facing issue when year is leap year and month is February.

I have already spent a day and half searching for solution but non are useful as they only help with case 1.

I also tried calculating total days between current date and dob with year base as 365.25 and then subtracting it from current date but facing the same issue.

is there any other approach i should look at?

Are you using the DATEDIFF function? What SQL can you add to your question to help us see what you've got so far? — GrandMasterFlush
the code is in my office's system.I will share it by tomorrow. basically i have used DATEADD function — PalakM
You share your efforts at the time you post your question. If you can't do that until tomorrow, then post your question tomorrow. And why did you need an image (which costs mobile users data charges and isn't visible behind many corporate proxies) to say I want to have the user enter their birthdate and display the number of years, months and days old they are? — Ken White
thank you for the suggestion @KenWhite but the issue i am facing is not with displaying the number of years,month and days from date of birth.I want to calculate date of birth from year,month and days input.so for better understanding of problem i attached the image.I will surely keep in mind to not include unnecessary graphical representation next time. — PalakM
The point is that you don't need an image, because it adds no value. Explain the problem in text. Images should only be used when there is no other way to describe or demonstrate the problem, for the reasons I've indicated. And that doesn't change the need to show your efforts in the post, not tomorrow. — Ken White

John Cappelletti John Cappelletti · Accepted Answer · 2017-11-01T17:53:58

If you are open to a TVF, I have a function to calculate elapsed time in years, months, days, hours, minutes, and seconds. You can remove (or ignore) hours, minutes, and seconds

Now, I have never considered resolving the reverse, but it can be done with the same function.

This function is very accurate and performant. You'll never have to worry about the miscelaneous date calculations.

In the examples below, I used today's date of 2017-11-01, but you can replace with GetDate()

Example 1

Declare @Date date = '1988-02-04'

Select * from [dbo].[tvf-Date-Elapsed](@Date,'2017-11-01')

Returns

Years   Months  Days    Hours   Minutes Seconds
29      8       28      0       0       0

Example 2

Declare @Y int  = 29
Declare @M int  = 8
Declare @D int  = 28

Select D=max(D)
 From  (
         Select Top (500) D= DateAdd(DAY,-1+Row_Number() over (Order by (Select NULL)), DateAdd(DAY,((@Y*366)+(@M*31)+@D)*-1,'2017-11-01'))
         From master..spt_values n1
        ) A
 Cross Apply [dbo].[tvf-Date-Elapsed](A.D,'2017-11-01')
 Where Years =@Y 
   and Months=IIF(@M=0,Months,@M)
   and Days  =IIF(@D=0,Days,@D)

Returns

D
1988-02-04 00:00:00.000

The UDF if Interested

CREATE FUNCTION [dbo].[tvf-Date-Elapsed] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
    with cteBN(N)   as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
         cteRN(R)   as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c),
         cteYY(N,D) as (Select Max(R),Max(DateAdd(YY,R,@D1))From cteRN R Where DateAdd(YY,R,@D1)<=@D2),
         cteMM(N,D) as (Select Max(R),Max(DateAdd(MM,R,D))  From (Select Top 12 R From cteRN Order By 1) R, cteYY P Where DateAdd(MM,R,D)<=@D2),
         cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,D))  From (Select Top 31 R From cteRN Order By 1) R, cteMM P Where DateAdd(DD,R,D)<=@D2),
         cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D))  From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
         cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
         cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D))  From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2)

    Select [Years]   = cteYY.N
          ,[Months]  = cteMM.N
          ,[Days]    = cteDD.N
          ,[Hours]   = cteHH.N
          ,[Minutes] = cteMI.N
          ,[Seconds] = cteSS.N
          --,[Elapsed] = Format(cteYY.N,'0000')+':'+Format(cteMM.N,'00')+':'+Format(cteDD.N,'00')+' '+Format(cteHH.N,'00')+':'+Format(cteMI.N,'00')+':'+Format(cteSS.N,'00')
     From  cteYY,cteMM,cteDD,cteHH,cteMI,cteSS
)
--Max 1000 years
--Select * from [dbo].[tvf-Date-Elapsed] ('1991-09-12 21:00:00.000',GetDate())
--Select * from [dbo].[tvf-Date-Elapsed] ('2017-01-01 20:30:15','2018-02-05 22:58:35')

Get past date before days / month / years using date functions in sql

1 Answers