Sreenivasa Reddy Thotli

SELECT STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' + STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ') AS HOUR, COUNT(*) AS COUNT FROM tableName WHERE Createddate BETWEEN CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE()))) AND CONVERT(DATETIME,FLOOR(CONVERT(FLOAT,GETDATE())) + 1) GROUP BY STUFF(RIGHT(CONVERT(VARCHAR,Createddate,0),7),3,3,' ') + ' - ' + STUFF(RIGHT(CONVERT(VARCHAR,DATEADD(HOUR,1,Createddate),0),7),3,3,' ') ORDER BY 1

CREATE TRIGGER [dbo].[TGRName] ON [dbo].[TableName] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF (UPDATE([ColumnName])) INSERT INTO [TableName]([Columns]....) Select distinct [Columns].... from INSERTED I INNER JOIN Deleted D ON D.Id= I.Id WHERE I.[ColumnName] <> D.[ColumnName] SET NOCOUNT OFF END

SELECT RandomNumber = CONVERT(VARCHAR,ABS(CHECKSUM(NEWID()))) FROM master.dbo.spt_values ORDER BY RandomNumber

CREATE function [dbo].[fn_WeekStartDate] ( @date datetime ) returns datetime as begin declare @dtweek int declare @weekstart DateTime Declare @NoOfDays int Declare @t varchar(100) set @dtweek = datepart(weekday, @date) -1 Set @weekstart = DateAdd(Day,-@dtweek,@date) if datepart(month,@weekstart) <> datepart(month,@date) BEGIN Set @t = Convert(VARCHAR,DatePart(month,DateAdd(month,0,@date))) + '/01/'+ Convert(VARCHAR,datepart(year,@date)) Set @NoOfDays = DAteDiff(day,@t,@date) Set @weekstart = DateAdd(Day,-@NoOfDays,@date) SELECT @weekstart = @t END return @weekstart end Ex: select dbo.[fn_WeekStartDate](getdate())

CREATE function [dbo].[fn_WeekEndDate] ( @date datetime ) returns datetime as begin declare @dtweek int declare @weekend DateTime Declare @NoOfDays int Declare @t varchar(100) set @dtweek = 7- datepart(weekday, @date) Set @weekend = DateAdd(Day,@dtweek,@date) if datepart(month,@weekend) <> datepart(month,@date) BEGIN Set @t = Convert(VARCHAR,DatePart(month,DateAdd(month,1,@date))) + '/01/'+ Convert(VARCHAR,datepart(year,@date)) Set @NoOfDays = DAteDiff(day,@t,@date) +1 Set @weekend = DateAdd(Day,-@NoOfDays,@date) END return @weekend end Ex : select dbo.[fn_WeekEndDate](getdate())

CREATE function [dbo].[SplitByString] ( @string nvarchar(4000), @delimiter nvarchar(10), @addDelimetrContent bit ) returns @table table ( [Value] nvarchar(4000) ) begin declare @nextString nvarchar(4000) declare @pos int, @nextPos int SET @nextString = '' SET @string = @string + @delimiter SET @pos = CHARINDEX(@delimiter, @string) SET @nextPos = 1 WHILE (@pos <> 0) BEGIN IF (@addDelimetrContent = 1) SET @nextString = @delimiter + SUBSTRING(@string, 1, @pos - 1) ELSE SET @nextString = SUBSTRING(@string, 1, @pos - 1) IF (@nextString != '' AND @nextString != @delimiter) BEGIN INSERT INTO @table ([Value]) VALUES (@nextString) END SET @string = SUBSTRING(@string, @pos + LEN(@delimiter), LEN(@string)) SET @nextPos = @pos SET @pos = CHARINDEX(@delimiter, @string) END RETURN END Ex : select * from dbo.[SplitByString]('http://www.google.comhttp://gmail.comhttp://yahoo.com'

CREATE FUNCTION [dbo].[udf_GetNumeric] (@strAlphaNumeric VARCHAR(256)) RETURNS VARCHAR(256) AS BEGIN DECLARE @intAlpha INT SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric) BEGIN WHILE @intAlpha > 0 BEGIN SET @strAlphaNumeric = STUFF(@strAlphaNumeric, @intAlpha, 1, '' ) SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric ) END END RETURN ISNULL(@strAlphaNumeric,0) END Ex : select dbo.[udf_GetNumeric]('xyz @abc123')