Share this post

Remove Trailing Zeros

CREATE FUNCTION [dbo].[udf_RemoveTrailingZeros]
(
	@strValue nvarchar(50),
	@KeepComma bit = 0,
	@RemoveBefore bit = 0
)
RETURNS nvarchar(50)
BEGIN

DECLARE @strValueR nvarchar(50)
DECLARE @strValueL nvarchar(50)
DECLARE @charInd int
DECLARE @intCount int

SET @charInd = CHARINDEX('.',@strValue)

IF @charInd = 0
 SET @strValueL = @strValue
ELSE
BEGIN
 SET @strValueR = RIGHT(@strValue, LEN(@strValue)-@charInd)
 SET @strValueL = LEFT(@strValue, @charInd-1)

 SET @intCount = LEN(@strValueR)+1
 WHILE @intCount > 0
 BEGIN
  SET @intCount = @intCount - 1
  IF SUBSTRING(@strValueR, @intCount, 1) NOT LIKE '0'
   BREAK
 END
 SELECT @strValueR = LEFT(@strValueR, @intCount)
END

SELECT @strValue = CASE WHEN @RemoveBefore = 1 THEN Cast(Cast(@strValueL as int) as varchar) ELSE @strValueL END +
 CASE WHEN (LEN(@strValueR) > 0) THEN
  '.' + @strValueR
 ELSE
  ''
 END

 --Replace point decimal by comma
 set @strValue = replace(@strValue,'.',',')

 IF @KeepComma = 1 AND SUBSTRING(@strValue, len(@strValue),1) <> ',' AND CHARINDEX(',',@strValue) <= 0
	SET @strValue = @strValue + ','

RETURN @strValue
END

Share this post

Get the last Day of the week

CREATE FUNCTION [dbo].[udf_GetLastDayOfWeek]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
	/* NE PAS OUBLIER DE FAIRE UN "SET DATEFIRST 1" AVANT d'appeler cette fonction */
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
	
	SELECT @ResultVar =	CASE datepart(weekday,@Date)
							WHEN 1 THEN CONVERT(VARCHAR(10),(@Date+4),120)
							WHEN 2 THEN CONVERT(VARCHAR(10),(@Date+3),120)
							WHEN 3 THEN CONVERT(VARCHAR(10),(@Date+2),120)
							WHEN 4 THEN CONVERT(VARCHAR(10),(@Date+1),120)
							WHEN 5 THEN CONVERT(VARCHAR(10),(@Date),120)
							WHEN 6 THEN CONVERT(VARCHAR(10),(@Date-1),120)
							WHEN 7 THEN CONVERT(VARCHAR(10),(@Date-2),120)
						END

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

Get the last day of the previous month

CREATE FUNCTION [dbo].[udf_GetLastDayOfPreviousMonth]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@Date)),@Date),120)

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

Get the Last Day of the month

CREATE FUNCTION [dbo].[udf_GetLastDayOfMonth]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),120)

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

Get the First Day of the week

CREATE FUNCTION [dbo].[udf_GetFirstDayOfWeek]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar = DATEADD(ww, DATEDIFF(ww,0,@Date), 0)

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

Get the First Day of the next month

CREATE FUNCTION [dbo].[udf_GetFirstDayOfNextMonth]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,1,@Date)),120)

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

Get the First Day of the month

CREATE FUNCTION [dbo].[udf_GetFirstDayOfMonth]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	SELECT @ResultVar = CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@Date)-1),@Date),120)

	-- Return the result of the function
	RETURN @ResultVar

END

Share this post

This function search a word in all the sysobjects and return the result with the source (storedprocedure, table, ...).

Thanks to my colleague Nicolas Pecqueux for this script, I've juste adapted it.

CREATE Function [dbo].[udf_Find] (@string varchar(100))
RETURNS @retTable TABLE 
(
    NameObject nvarchar(128) NULL,
    ObjectType nvarchar(100) NULL,
    NameParent nvarchar(128) NULL,
    ObjectTypeParent nvarchar(100) NULL,
    DataBaseName nvarchar(128) NULL
)
AS
BEGIN
INSERT INTO @retTable(NameObject, ObjectType, NameParent, ObjectTypeParent, DataBaseName) 
SELECT	s.[Name] AS [Name],
		ObjectType =	CASE s.Type
							when 'D' then 'Default or DEFAULT constraint' 
							when 'FN' then 'Scalar function' 
							when 'IF' then 'Inlined table-function' 
							when 'P' then 'Stored procedure' 
							when 'R' then 'Rule' 
							when 'RF' then 'Replication filter stored procedure' 
							when 'TF' then 'Table function' 
							when 'TR' then 'Trigger' 
							when 'U' then 'User table' 
							when 'V' then 'View' 
							when 'X' then 'Extended stored procedure'
							when 'PK' then 'Primary Key'
							when 'F' then 'Foreign Key'
							else 'others'
						END,
		s2.Name AS [Name Parent],
		ObjectTypeParent =	CASE IsNull(s2.Type,'')
								when 'D' then 'Default or DEFAULT constraint' 
								when 'FN' then 'Scalar function' 
								when 'IF' then 'Inlined table-function' 
								when 'P' then 'Stored procedure' 
								when 'R' then 'Rule' 
								when 'RF' then 'Replication filter stored procedure' 
								when 'TF' then 'Table function' 
								when 'TR' then 'Trigger' 
								when 'U' then 'User table' 
								when 'V' then 'View' 
								when 'X' then 'Extended stored procedure'
								when 'PK' then 'Primary Key'
								when 'F' then 'Foreign Key'
								WHEN '' THEN null
								else 'others'
							END,
		(Select TOP 1 TABLE_CATALOG From Information_schema.Tables) AS DataBaseName
	FROM sysobjects s
	LEFT JOIN sysobjects s2 ON s2.id = s.parent_obj
	WHERE	s.id IN (SELECT id FROM syscomments where [text] LIKE '%'+@string+'%')
		OR	s.id IN (SELECT object_id FROM sys.all_columns WHERE [name] LIKE '%'+@string+'%')
		OR	s.id IN (SELECT parent_obj FROM [sysobjects] WHERE [name] LIKE '%'+@string+'%')
		OR	s.name LIKE '%'+@string+'%'
UNION
	SELECT	sjs.[step_name] COLLATE Latin1_General_CI_AS AS [Name], 
			ObjectType = 'Job Step SQL',
			sj.[name] COLLATE Latin1_General_CI_AS AS [Name Parent],
			ObjectTypeParent = 'Job SQL',
			sjs.database_name COLLATE Latin1_General_CI_AS AS DataBaseName
	FROM msdb.dbo.sysjobs sj
	LEFT JOIN msdb.dbo.[sysjobsteps] sjs ON sj.[job_id] = sjs.[job_id]
	WHERE	sjs.[command] LIKE '%'+@string+'%'
		OR	sjs.[step_name] LIKE '%'+@string+'%'

RETURN
END

Example and result :

SELECT * FROM dbo.udf_Find('Theme')

Share this post

This function return a varchar with the date and time from a datetime parameter.

The string returned is in french but can be adapted in other language.

CREATE FUNCTION [dbo].[ConvertDateToVarchar]
(	
	@Date DateTime
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT	DateStr =	CASE	WHEN @Date Is Null THEN 'Jamais' 
								WHEN (DatePart(hour, @Date) = 0 AND DatePart(Minute, @date) = 0) THEN CONVERT(VARCHAR, @Date, 105)
						ELSE	CONVERT(VARCHAR, @Date, 105) + ' à ' + 
								REPLACE(CONVERT(VARCHAR(5), @Date, 108),':', 'h')
						END
)

Share this post

Séjours organisés à l'île Maurice.