CODEWITHSUNDEEP
sqlsql-serversql-function
dotnetdevcsharp
dotnetdevcsharp

Reputation: 3980

returning weird dates function to calculate length of serice

My function below is working for some dates but not others some, its printing minus years for some reason ie., 365 days etc.

ALTER FUNCTION [dbo].[hmsGetLosText](@FromDt as datetime) returns varchar(255)
as

BEGIN
DECLARE @YRS AS SMALLINT, @MNT AS SMALLINT, @DY AS SMALLINT, @Fvalue varchar(255)

SET @YRS = DATEDIFF(YY, @FromDt,GETDATE())
SET @MNT = DATEDIFF(MM, @FromDt,GETDATE()) % 12
SET @DY = (DATEDIFF(DD,GETDATE(), DATEADD(MM, (@YRS * 12) + @MNT,@FromDt) )*-1)


SET @Fvalue = CAST(@YRS AS VARCHAR) + ' y ' + CAST(@MNT AS VARCHAR) + ' m ' + CAST(@DY AS VARCHAR) + ' d '
RETURN @Fvalue

Upvotes: 0

Views: 72

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

I would use:

set @YRS = DATEDIFF(year,@FromDt,GETDATE()) +
        CASE WHEN DATEADD(year,DATEDIFF(year,@FromDt,GETDATE()),@FromDt) > GETDATE()
             THEN -1 ELSE 0 END
set @FromDt = DATEADD(year,@YRS,@FromDt)

set @MNT = DATEDIFF(month,@FromDt,GETDATE()) +
        CASE WHEN DATEADD(month,DATEDIFF(month,@FromDt,GETDATE()),@FromDt) > GETDATE()
             THEN -1 ELSE 0 END
set @FromDt = DATEADD(month,@MNT,@FromDt)

set @DY = DATEDIFF(day,@FromDt,GETDATE())

For me, these are far more readily understood - we first get a years value that we're happy with, then add that onto @FromDt. Then we can do a direct DATEDIFF for months without much fussing around with modulo arithmetic. Similarly, once we're happy with a months value, we adjust @FromDt again so that a simple DATEDIFF gives us the correct number of days.

(You can eliminate some further calculation by first setting @YRS and then working out whether to adjust it, but not sure if that's needed here)

The reason we have to do the slight fiddle around to get the correct @YRS and @MNT values is that DATEDIFF:

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Which is to say, that DATEDIFF(year,'20121231','20130101') returns 1 (as does DATEDIFF(year,'20120101','20131231')). Which is correct per its own definition. But we want a value that never overshoots if we add that value back onto the earlier of the two dates - so we have to compensate by sometimes subtracting 1.

Upvotes: 2

Related Questions