มังกรเหิรนภา: SQL Server Query : Convert int value into hour str...

มังกรเหิรนภา: SQL Server Query : Convert int value into hour str...: USE [test] GO /****** Object:  UserDefinedFunction [dbo].[func_convertint_to_hourstring]    Script Date: 08/31/2014 04:01:08 ******/ SET...

มังกรเหิรนภา: SQL Server Query : Convert double value into hour ...

มังกรเหิรนภา: SQL Server Query : Convert double value into hour ...: USE [test] GO /****** Object:  UserDefinedFunction [dbo].[func_convertdouble_to_hoursint]    Script Date: 08/31/2014 02:43:40 ******/ SE...

SQL Server Query : Convert int value into hour string format.

USE [test]
GO
/****** Object:  UserDefinedFunction [dbo].[func_convertint_to_hourstring]    Script Date: 08/31/2014 04:01:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION [dbo].[func_convertint_to_hourstring]
(
-- Add the parameters for the function here
@minutesint int
)
RETURNS nvarchar(5)
AS
BEGIN
declare @hourpart nvarchar(2);
declare @minutepart nvarchar(2);
declare @show nvarchar(5);

-- convert int to hour string
set @hourpart = @minutesint / 60;
set @minutepart = @minutesint % 60;
if(LEN(@minutepart) < 2)
begin
set @minutepart = '0' + @minutepart;
end;
set @show = @hourpart + ':' + @minutepart;

-- Return the result of the function
RETURN @show;
END
go

declare @ret nvarchar(5);
exec @ret = [func_convertint_to_hourstring] 390;
select @ret;

-- output = 6:30

SQL Server Query : Convert double value into hour int format.

USE [test]
GO
/****** Object:  UserDefinedFunction [dbo].[func_convertdouble_to_hoursint]    Script Date: 08/31/2014 02:43:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
Create FUNCTION [func_convertdouble_to_hoursint]
(
-- Add the parameters for the function here
@hoursdecimal decimal(23, 10)
)
RETURNS decimal(23, 2)
AS
BEGIN
declare @minutesint int;
declare @hourpart int;
declare @minutepart int;
declare @ret decimal(23, 2);

-- convert double to hour int format
set @minutesint = convert(int, (@hoursdecimal * 60));
set @hourpart = @minutesint / 60;
set @minutepart = @minutesint % 60;
set @ret = @hourpart + (@minutepart / 100.00);

-- Return the result of the function
RETURN @ret;
END
go

declare @ret decimal(23, 2);
exec @ret = [func_convertdouble_to_hoursint] 6.5;
select @ret;

-- output = 6.30