标签云

微信群

扫码加入我们

WeChat QR Code

I wrote a SQL function to convert a datetime value in SQL to a friendlier "n Hours Ago" or "n Days Ago" etc type of message. And I was wondering if there was a better way to do it.

(Yes I know "don't do it in SQL" but for design reasons I have to do it this way).

Here is the function I've written:

CREATE FUNCTION dbo.GetFriendlyDateTimeValue
(
    @CompareDate DateTime
)
RETURNS nvarchar(48)
AS
BEGIN
    DECLARE @Now DateTime
    DECLARE @Hours int
        DECLARE @Suff nvarchar(256)
    DECLARE @Found bit

    SET @Found = 0
    SET @Now = getDate()
    SET @Hours = DATEDIFF(MI, @CompareDate, @Now)/60

    IF @Hours <= 1
    BEGIN
        SET @Suff = 'Just Now'
        SET @Found = 1
        RETURN @Suff
    END

    IF @Hours < 24
    BEGIN
        SET @Suff = ' Hours Ago'
        SET @Found = 1
    END

    IF @Hours >= 8760 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 8760
        SET @Suff = ' Years Ago'
        SET @Found = 1
    END

    IF @Hours >= 720 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 720
        SET @Suff = ' Months Ago'
        SET @Found = 1
    END

    IF @Hours >= 168 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 168
        SET @Suff = ' Weeks Ago'
        SET @Found = 1
    END

    IF @Hours >= 24 AND @Found = 0
    BEGIN
        SET @Hours = @Hours / 24
        SET @Suff = ' Days Ago'
        SET @Found = 1
    END

    RETURN Convert(nvarchar, @Hours) + @Suff
END


Did you try this? I don't believe you can call 'getDate()' from a function in t-sql

2018年08月16日15分31秒

I did try it. Just tried it again on SQL 2005, 2008 and 2008R2 CTP. Works. GETDATE makes the function non-deterministic which limits its use a little. From SQL2000 BOL: "In Microsoft SQL Server 2000, nondeterministic functions cannot be specified in two types of Transact-SQL expressions: * An index cannot be created on a computed column if the computed_column_expression references any nondeterministic functions. * A clustered index cannot be created on a view if the view references any nondeterministic functions."

2018年08月16日15分31秒

Pretty sure this would not work on SQL 2000 since non-deterministic UDFs were new for 2005, but there are hack-arounds for that even. Worst case, you could mod the UDF to add a second datetime parameter were you'd pass in GETDATE().

2018年08月16日15分31秒