CODEWITHSUNDEEP
sqlsql-serverdatabasedatetime
Chris Cherryholme
Chris Cherryholme

Reputation: 35

Calculate the amount of time between two dates and return a true/false if it's been over/under X amount of years

I'd like to create a query that would take a field (datetime) and compare it to the current time (using GETDATE()). If it's been 10 years since that field and today, return true, else false.

I tried using the DATEDIFF() function, but it doesn't seem to take into account months/days. Does anyone have any tips?

Upvotes: 0

Views: 522

Answers (2)

SteveC
SteveC

Reputation: 6015

Datediff should do the job if the datepart parameter is set to 'year'.

declare @field      datetime='2001-01-01';

select case when datediff(year, @field, getdate())>=10 then 'True' else 'False' end;

Output

True

[EDIT] Alternately, if you like the actual number of 365 day periods between a datetime and GETDATE, you could use the datepart 'day' and divide by 365.

Query

select case when datediff(day, @field, getdate())/365>=10 then 'True' else 'False' end

Output

True

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

SQL Server doesn't really have "true" and "false". I would use 1 and 0:

select (case when field < dateadd(year, -10, getdate()) then 1 else 0 end) as flag_10yr

Note that this does direct comparisons of the dates rather than datediff(). That function counts the number of boundaries between two years, which is not the same thing -- 2019-12-31 and 2020-01-01 are "1 year apart" according to this function.

Upvotes: 2

Related Questions