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:
Post a Comment