27,580
社区成员
发帖
与我相关
我的任务
分享
--考虑到小时可能为0的情况:
create function f_getInfo(@hour int)
returns varchar(8000)
begin
declare @str varchar(8000)
select @str=
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(yy,yy)-1900=0 or @hour%24=0
then
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)
else
rtrim(datediff(dd,rtrim(datepart(yy,yy))+'-01-01',yy))+'天'
end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24 as datetime) else 0 end) as yy,@hour%24 as hh) t
return @str
end
go
select dbo.f_getinfo(10) --10小时
select dbo.f_getinfo(100) --4天4小时
select dbo.f_getinfo(1000) --1月10天16小时
select dbo.f_getinfo(10008) --1年1月21天
select dbo.f_getinfo(100008) --11年4月30天
go
drop function f_getinfo
go
create function f_getInfo(@hour int)
returns varchar(8000)
begin
declare @str varchar(8000)
select @str=
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(yy,yy)-1900=0
then
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)
else
rtrim(datediff(dd,rtrim(datepart(yy,yy))+'-01-01',yy))+'天'
end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24 as datetime) else 0 end) as yy,@hour%24 as hh) t
return @str
end
go
select dbo.f_getinfo(10) --10小时
select dbo.f_getinfo(100) --4天4小时
select dbo.f_getinfo(1000) --1月10天16小时
select dbo.f_getinfo(10000) --1年51天16小时
select dbo.f_getinfo(100000) --11年149天16小时
go
drop function f_getinfo
go
-- =============================================
-- Author: <Author:ailly>
-- =============================================
ALTER FUNCTION GetTime(@var int)
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @y int,@m int,@d int,@h int,@TimeStr NVARCHAR(80)
IF @var>365*24
BEGIN
SELECT @y=@var/(365*24),@var=@var%(365*24)
SELECT @m=@var/(30*24),@var=@var%(30*24)
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@y AS NVARCHAR(20))+'年'+CAST(@m AS NVARCHAR(20))+'月'+CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
ELSE IF @var>30*24
BEGIN
SELECT @m=@var/(30*24),@var=@var%(30*24)
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@m AS NVARCHAR(20))+'月'+CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
ELSE
BEGIN
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
RETURN (SELECT @TimeStr)
END
SELECT 时间1=[dbo].[GetTime] (25), 时间2=[dbo].[GetTime] (240),时间3=[dbo].[GetTime] (1000),时间4=[dbo].[GetTime] (10000)
/*
时间1 时间2 时间3 时间4
------------------------------ ------------------------------ ------------------------------ ------------------------------
1天1小时 10天0小时 1月11天16小时 1年1月21天16小时
(1 行受影响)
*/
-- =============================================
-- Author: <Author:ailly>
-- =============================================
CREATE FUNCTION GetTime(@var int)
RETURNS NVARCHAR(30)
AS
BEGIN
DECLARE @y int,@m int,@d int,@h int,@TimeStr NVARCHAR(80)
IF @var>365*24
BEGIN
SELECT @y=@var/(365*24),@var=@var%(365*24)
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@y AS NVARCHAR(20))+'年'+CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
ELSE IF @var>30*24
BEGIN
SELECT @m=@var/(30*24),@var=@var%(30*24)
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@m AS NVARCHAR(20))+'月'+CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
ELSE
BEGIN
SELECT @d=@var/24,@h=@var%24
SET @TimeStr=CAST(@D AS NVARCHAR(20))+'天'+CAST(@h AS NVARCHAR(20))+'小时'
END
RETURN (SELECT @TimeStr)
END
SELECT 时间1=[dbo].[GetTime] (25), 时间2=[dbo].[GetTime] (240), 时间3=[dbo].[GetTime] (10000)
/*
时间1 时间2 时间3
------------------------------ ------------------------------ ------------------------------
1天1小时 10天0小时 1年51天16小时
(1 行受影响)*/
create function f_getInfo(@hour int)
returns varchar(8000)
begin
declare @str varchar(8000)
select @str=
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24 as datetime) else 0 end) as yy,@hour%24 as hh) t
return @str
end
go
select dbo.f_getinfo(10) --10小时
select dbo.f_getinfo(100) --4天4小时
select dbo.f_getinfo(1000) --1月10天16小时
select dbo.f_getinfo(10000) --1年1月20天16小时
select dbo.f_getinfo(100000) --11年4月29天16小时
go
drop function f_getinfo
go
exec up_get 18018
/*
2年20日18小时
*/
Create proc up_get
@Hour int
as
declare @date datetime
select @date=dateadd(hh,@Hour,0)
print @date
select isnull(cast(nullif(datediff(yy,0,@date),0) as varchar(10))+'年','')+
isnull(cast(nullif(datepart(mm,@date)-1,0) as varchar(10))+'月','')+
isnull(cast(nullif(datepart(dd,@date)-1,0) as varchar(10))+'日','')+
isnull(cast(nullif(datepart(hour,@date),0) as varchar(10))+'小时','')
GO
exec up_get 25
--1日1小时
exec up_get 125
--5日5小时
exec up_get 9600
--1年1月4日
exec up_get 9603
--1年1月4日3小时
declare @hour int
set @hour=10
select
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24-1 as datetime) else 0 end) as yy,@hour%24 as hh) t
/*
----------------------------------------------------------
10小时
*/
set @hour=100
select
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24-1 as datetime) else 0 end) as yy,@hour%24 as hh) t
/*
----------------------------------------------------------
3天4小时
*/
set @hour=1000
select
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24-1 as datetime) else 0 end) as yy,@hour%24 as hh) t
/*
----------------------------------------------------------
1月9天16小时
*/
set @hour=10000
select
(case when datepart(yy,yy)-1900=0 then '' else rtrim(datepart(yy,yy)-1900)+'年' end)+
(case when datepart(mm,yy)-1 =0 then '' else rtrim(datepart(mm,yy)-1) +'月' end)+
(case when datepart(dd,yy)-1 =0 then '' else rtrim(datepart(dd,yy)-1) +'天' end)+
(case when hh =0 then '' else rtrim(hh) +'小时' end)
from
(select (case when @hour/24>0 then cast(@hour/24-1 as datetime) else 0 end) as yy,@hour%24 as hh) t
/*
----------------------------------------------------------
1年1月19天16小时
*/