Wednesday, November 24, 2010

Format date from MM/DD to MM/DD/YYYY

CREATE FUNCTION [dbo].[FORMATEDATE](


@INPUT VARCHAR(100),

@FORMAT VARCHAR(100)

)

RETURNS VARCHAR(100)

AS

BEGIN

DECLARE @DAY VARCHAR(100),@MONTH VARCHAR(100),@YEAR VARCHAR(100)



IF @INPUT IS NOT NULL

BEGIN

IF(UPPER(@FORMAT) = 'MM/DD')

BEGIN

SET @MONTH = SUBSTRING(@INPUT,0,CHARINDEX('/',@INPUT))

SET @DAY = SUBSTRING(@INPUT,CHARINDEX('/',@INPUT)+1,LEN(@INPUT))



IF(@MONTH > MONTH(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE IF(@MONTH = MONTH(GETDATE()) AND @DAY >= DAY(GETDATE())) SELECT @YEAR = YEAR(GETDATE())

ELSE SELECT @YEAR = YEAR(GETDATE()) + 1



IF(@MONTH = 2 AND @DAY > 28)

BEGIN

IF (SELECT DBO.[ufn_IsLeapYear](@YEAR)) <> 1

BEGIN

SET @YEAR = @YEAR + 4-@YEAR%4

END

END

SET @INPUT = @MONTH + '/' + @DAY + '/' + @YEAR

END

END

RETURN @INPUT



END

GO

dump transaction in sqlserver 2008

-- =============================================
-- Author: Asif Ghanchi
-- Create date: 11/24/2010
-- Description: Dump transaction for Sql Server 2008
-- =============================================
CREATE PROCEDURE dump_transaction
@database_name varchar(100)
AS
BEGIN

declare @name varchar(100)

select @name = name from sys.database_files where type = 1

declare @query nvarchar(max)

SET @query = 'USE '+ @database_name +';

ALTER DATABASE '+ @database_name +'
SET RECOVERY SIMPLE;

DBCC SHRINKFILE ('''+ @name +''');

ALTER DATABASE '+ @database_name +'
SET RECOVERY FULL;'

exec sp_executesql @query

END