marco
94 lines
2.5 KiB
Transact-SQL
94 lines
2.5 KiB
Transact-SQL
set ANSI_NULLS on;
|
|
go
|
|
|
|
create FUNCTION f_expDataGGMMAAAA( @Data AS DATE )
|
|
-- ritorna la DATA per file di export x paghe formato carattere
|
|
RETURNS CHAR(8)
|
|
AS
|
|
BEGIN
|
|
RETURN REPLACE( CONVERT(NVARCHAR(50), @Data , 105), '-','')
|
|
END
|
|
go
|
|
|
|
create FUNCTION f_expTimeHHMM( @Time AS DECIMAL(10,5) , @Format CHAR(1), @Arrot AS INT)
|
|
-- ritorna ore per file di export x paghe formato carattere es . 8 0re 30 minuti -> 0850
|
|
-- Time valore da convertire
|
|
-- Format Formato del valore passato ( M-minuti O-Ore )
|
|
RETURNS CHAR(4)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @Ore AS INT;
|
|
DECLARE @Min AS INT;
|
|
DECLARE @OreMin AS DECIMAL(10,5);
|
|
|
|
IF @Format = 'M' -- Minuti
|
|
BEGIN
|
|
-- SET @Ore = CONVERT( INTEGER , ( @Time / 60.00 ) );
|
|
-- SET @Min = @Time - ( @Ore * 60 );
|
|
|
|
SET @OreMin = CONVERT(INT, (@Time / @Arrot) )
|
|
SET @OreMin = ( ( @OreMin * @Arrot ) / 60 ) * 100
|
|
END
|
|
|
|
RETURN dbo.f_padLeft( CONVERT( VARCHAR(4) , CONVERT(INT, @OreMin )) , 4, '0')
|
|
END
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_padLeft
|
|
*
|
|
* fornisce una stringa della lunghezza desiderata aggiungendo a sx il carattere richiesto alla @string originale
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.03.19
|
|
*
|
|
****************************************/
|
|
create FUNCTION f_padLeft (@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
|
|
RETURNS VARCHAR(255) AS
|
|
BEGIN
|
|
|
|
-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
|
|
RETURN CASE
|
|
WHEN LEN(@string) < @desired_length
|
|
THEN REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character) + @string
|
|
ELSE @string
|
|
END
|
|
|
|
END
|
|
go
|
|
|
|
/***************************************
|
|
* FUNCTION f_padLeft
|
|
*
|
|
* fornisce una stringa della lunghezza desiderata aggiungendo a sx il carattere richiesto alla @string originale
|
|
*
|
|
* Steamware, S.E.L.
|
|
* mod: 2010.03.19
|
|
*
|
|
****************************************/
|
|
create FUNCTION f_padRight (@string VARCHAR(255), @desired_length INTEGER, @pad_character CHAR(1))
|
|
RETURNS VARCHAR(255) AS
|
|
BEGIN
|
|
|
|
-- Prefix the required number of spaces to bulk up the string and then replace the spaces with the desired character
|
|
RETURN CASE
|
|
WHEN LEN(@string) < @desired_length
|
|
THEN @string + REPLACE(SPACE(@desired_length - LEN(@string)), ' ', @pad_character)
|
|
ELSE @string
|
|
END
|
|
|
|
END
|
|
go
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-- registro versione...
|
|
INSERT INTO [dbo].[LogUpdateDb] ([Versione],[Data]) VALUES(312, GETDATE())
|
|
GO
|
|
SELECT * FROM LogUpdateDb ORDER BY Versione DESC
|