从GETDATE()减去日期以获得“time since”

I have a elements that have values in DATETIME format in column 'Date Created'.

我有一个元素，它的值是DATETIME格式的“创建日期”列。

I would like to be able to extract value how long time ago it was from time at which query is executed (as a reference take GETDATE())

我希望能够提取值，在多长时间之前，查询被执行的时间(作为参考获取GETDATE())

When executing this:

在执行:

UPDATE #LPs
SET LastUsed = (GETDATE() - DateCreated)

I am getting 1900-01-01, I believe that is due to SQL Server limitations for minimal DATETIME.

我得到的是1900-01-01，我认为这是由于SQL Server限制了最小的DATETIME。

How should I right is so I can get values like "2 years 3 months", "2 months" (any datetime format)?

我应该怎样做才能得到“2年3个月”、“2个月”(任何datetime格式)之类的值?

2 个解决方案

#1

SELECT DATEDIFF(day, DateCreated, GETDATE()) 
FROM table

maybe it works

也许它工作

#2

Sql server does not have a built in function that returns date differences as a formatted string like you want, but it does have a DATEDIFF function that will return the number of date parts between two dates:

Sql server没有内置函数，它以格式化字符串的形式返回日期差异，但它确实有一个DATEDIFF函数，它将返回两个日期之间的日期部分:

DECLARE @Now datetime = GETDATE(),
        @Date datetime = GETDATE() + 5 -- five days from now

SELECT  @Now as Now,
        @Date as Date,
        DATEDIFF(Day, @Now, @Date) As DateDiff

You can get the number of months between the dates and calculate the string yourself using the modulu operator

您可以获取日期之间的月数，并使用modulu运算符自己计算字符串

#1

SELECT DATEDIFF(day, DateCreated, GETDATE()) 
FROM table