Thursday, May 17, 2018

fnDaysLast:: convert days to descriptive string

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnDaysLast]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnDaysLast]
GO

/*
Sunday=1, Monday=2, ....Saturday=6
*/
CREATE FUNCTION [dbo].[fnDaysLast]
(
@Days int
)
RETURNS varchar(50)
BEGIN
declare
@result varchar(50) = '',
@year int,
@month int

select @year = @Days / 365
if @year > 99
begin
select
@result = '> 99 year ',
@Days = 0
end
else if @year > 0
begin
select
@result = @result + CAST(@year as varchar) + ' year ',
@Days = @Days - @year * 365
end

select @month = @Days / 30
if @month > 0
begin
select
@result = @result + RIGHT(' ' + CAST(@month as varchar), 2) + ' month ',
@Days = @Days - @month * 30
end
if @year = 0
begin
select @result = @result + RIGHT(' ' + CAST(@Days as varchar), 2) + ' day'
end
/*
if @Days < 30
begin
select @result = CAST(@Days as varchar) + ' days'
end
else
begin
select @result = CAST(CAST(@Days / 30.0 AS NUMERIC(10, 1)) as varchar) + ' months'
end
*/

if @Days IS NULL
begin
select @result = 'Unknown'
end
if @result = ''
begin
select @result = 'No usage'
end

  return @result
END
GO

select
D3225=dbo.fnDaysLast(123225),
D1854=dbo.fnDaysLast(1854),
D200=dbo.fnDaysLast(200),
D30=dbo.fnDaysLast(33),
D28=dbo.fnDaysLast(28)

No comments: