This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(35.2, 89%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKG%3C/text%3E%3C/svg%3E)
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
Check an example:
declare @today datetime = '2023-01-01'
select dateadd(month, -6, dateadd(year, -64, @today))
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?
Maybe an interval of 6 months is not always 180 days. Use months or days according to your specific needs.
Thanks for your help
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.