Calculate the Date Age forumula

Question

Calculate the Date Age forumula

Kenny Gua 411

Hi, I want to know if today date is '2023-01-01' then I want to deduct 64 year and 6 month to know what will the date (yyyy-mm-dd'). Please provide the query to calculate.

If today date is : 2023-01-01
Assuming less 64.5 year wil be :1958-06-30

Answer 1

Viorel 106.4K

Check an example:

declare @today datetime = '2023-01-01'  
  
select dateadd(month, -6, dateadd(year, -64, @today))

Answer 2

Kenny Gua 411

if we do calculation from 6 month then get 1958-07-01 result from as per your query and
if we do calculation from 180 days then get 1958-06-30 result. Why one day difference from these two?

Viorel 106.4K Reputation points

2022-10-24T17:57:30.673+00:00

Maybe an interval of 6 months is not always 180 days. Use months or days according to your specific needs.
Kenny Gua 411 Reputation points

2022-10-24T19:19:40.697+00:00

Thanks for your help
Erland Sommarskog 93,056 Reputation points

2022-10-24T21:44:05.603+00:00
if we do calculation from 180 days then get 1958-06-30 result. Why one day difference from these two?

With only 180 days,

SELECT dateadd(DAY, -180, dateadd(YEAR, -64, '20230101'))

You seem to get 1958-07-05.

As you may know some months have 31 days a year and others have 30. And one has 28 or 29 days.

In some contexts, for instance interest calculation it is not uncommon to apply a method like 30/360, which means that you assume all months are
30 days, and that there are only 360 days a year. (Essentially this means that the 31st of all months is free of interest. And 28 Feb counts as three days.)

We don't know your context, and you original post only asked for going back 64 years and 6 months. But since you indicated an expected result, a better query might be:

select dateadd(day, -1, dateadd(month, -6, dateadd(year, -64, @today)))

By the way, in the field of interest calculation 30/360 is by no means universal, but there are a few more methods, including ACTUAL/ACTUAL.

Calculate the Date Age forumula

2 answers

Activity