Menu
I receive reports in which the data is ETL to the DB automatically. I extract and transform some of that data to load it somewhere else. One thing I need to do is a DATEDIFF but the year needs to be exact (i.e., 4.6 years instead of rounding up to five years.
The following is my script:
select *, DATEDIFF (yy, Begin_date, GETDATE()) AS 'Age in Years'
from Report_Stage;
The 'Age_In_Years' column is being rounded. How do I get the exact date in years?
755
In MS SQL Server, the DATEDIFF function is used to get the difference between two dates in terms of years, months, days, hours, minutes etc. SELECT DATEDIFF(day, '2018-03-13', GETDATE()) AS "Difference in days"
The DATEDIFF() function returns an integer value that represents the difference between the start date and end date, with the date part as the unit. If the result is out of range for the integer (-2,147,483,647), the DATEDIFF() function returns an error. Here, the DATEDIFF BIG() function should be used instead.
We can use DATEADD() function like below to Subtract Years from DateTime in Sql Server. DATEADD() functions first parameter value can be year or yyyy or yy, all will return the same result.
All datediff() does is compute the number of period boundaries crossed between two dates. For instance
datediff(yy,'31 Dec 2013','1 Jan 2014')
returns 1.
You'll get a more accurate result if you compute the difference between the two dates in days and divide by the mean length of a calendar year in days over a 400 year span (365.2425):
datediff(day,{start-date},{end-date},) / 365.2425
For instance,
select datediff(day,'1 Jan 2000' ,'18 April 2014') / 365.2425
return 14.29461248 — just round it to the desired precision.
140
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
