Calculate time from begin date in SQL

I’m trying to tally up the number of unique ID accounts that are active for more than a year as well as include how long each one is active. The problem with the code I have is it’s not including accounts that are currently active (ones that don’t have an end date). For example, if the begin date was May 01 2018 but has no end date since it’s currently active, it should also be included in this query. Here’s what I have so far..

SELECT UniqueID,
DATEDIFF(yy, Begin_Date,End_Date) as timeactive
FROM TABLE
WHERE DATEDIFF(yy, Begin_Date,End_Date) > 1

I want my output to look like…

Unique ID    Time active
F000012      2.5
F000031      1.5

This is what ended up working:

SELECT UniqueID,
    CAST(ROUND(DATEDIFF(day, Begin_Date, COALESCE(End_Date, getdate()))/365.0, 1, 0) AS NUMERIC (10,1)) as timeactive
    FROM TABLE
    WHERE DATEDIFF(day, Begin_Date, COALESCE (End_Date, getdate())) >= 365

Answer

If the EndDate is null then the output of the DateDiff function will be null, and any null compared to anything (even another null) is a result of null (usually then interpreted as false)

I suggest you use COALESCE to convert your end date to today if it is null:

SELECT 
  UniqueID,
  DATEDIFF(yy, Begin_Date,COALESCE(End_Date, GetUtcDate()) as timeactive
FROM TABLE
WHERE DATEDIFF(yy, Begin_Date,COALESCE(End_Date, GetUtcDate()) > 1

You should bear in mind that the DATEDIFF function as used here, in SQLserver does NOT return the amount of time that has passed between the two dates. It returns the number of times the named interval has changed between the two dates

For example, DATEDIFF(year, 2000-01-01, 2000-12-31 23:59:59) will return 0 because these are both year 2000 even though they’re just one second short of being a year apart. If you do DATEDIFF(year, 2000-12-31 23:59:59, 2001-01-01 00:00:01) even though these dates are only two seconds apart datediff will report them as 1 year apart because the year number has changed from 2000 to 2001.

DATEDIFF counts up by one every time the clock rolls past an interval change and in this case the interval is Year

To get your dates to report as 1.5 years etc you should consider to datediff by a smaller interval and divide, such as asking for the DAYS diff between two dates and then dividing by 365.25- the average number of days in a year. The smaller the interval you ask datediff for the more accurate the result will be but it’ll never be 100%. If you’re only after results to one decimal place of a year then days will be accurate enough

To get 1 decimal place, cast to a numeric with 1 DP:

SELECT 
  UniqueID,
  CAST(DATEDIFF(day, Begin_Date,COALESCE(End_Date, GetUtcDate())/365.25 AS NUMERIC(5,1)) as timeactive
FROM TABLE
WHERE DATEDIFF(day, Begin_Date,COALESCE(End_Date, GetUtcDate()) >= 365