Wednesday, May 18, 2011

Date Format for Financial Year


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Nitin Kurle
-- Create date: May 18 2011 12:02:25:140PM
-- Description: Here to Display Financial Year Date Range from Current Year to Previuos Year.
-- =============================================
CREATE PROCEDURE [dbo].[prDateCombination] (
@Dt as varchar(15)
)
AS
DECLARE
@FromDate as varchar(15),
@ToDate as varchar(15),
@PrevFromDate as varchar(15),
@PrevToDate as varchar(15),
@DebugMode as int,
@strSql as nvarchar(max),
@CurrentYrDt as varchar(15),
@PreviousYrDt as varchar(15)
BEGIN
Print 'Dates Start ==>> ' + convert(varchar,getdate(), 109)

set @FromDate = N''
set @ToDate = N''
IF MONTH(@Dt) >= 4  SELECT @FromDate = CONVERT(CHAR(4), YEAR(@Dt)) + '-04-01'
ELSE SELECT @FromDate = CONVERT(CHAR(4), YEAR(@Dt)-1) + '-04-01'
PRINT ' @FromDate ----->>>>  '+@FromDate

set @ToDate = cast((year(@Dt)) as varchar) + '-' + cast(month(@Dt) as varchar) + '-' + cast(day(@Dt) as varchar)
PRINT ' @ToDate ----->>>>  '+@ToDate

IF MONTH(@Dt) >= 4  SELECT @PrevFromDate = CONVERT(CHAR(4), YEAR(@Dt)-1) + '-04-01'
ELSE SELECT @PrevFromDate = CONVERT(CHAR(4), YEAR(@Dt)-2) + '-04-01'
PRINT ' @PrevFromDate ----->>>>  '+@PrevFromDate

set @PrevToDate = cast((year(@Dt)-1) as varchar) + '-' + cast(month(@Dt) as varchar) + '-' + cast(day(@Dt) as varchar)
PRINT ' @PrevToDate ----->>>>  '+@PrevToDate
PRINT ' -------------------------------------------------------------------- '

set @PreviousYrDt = N''
set @CurrentYrDt = N''

set @CurrentYrDt = convert(varchar,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@ToDate)+1,0)),23)
PRINT ' @CurrentYrDt ----->>>>  '+@CurrentYrDt

set @PreviousYrDt = cast((YEAR(@CurrentYrDt)-1) as varchar) + '-' + cast(MONTH(@CurrentYrDt) as varchar) + '-' + cast(DAY(@CurrentYrDt) as varchar)
PRINT ' @PreviousYrDt ----->>>>  '+@PreviousYrDt

Print 'Dates Complete ==>> ' + convert(varchar,getdate(), 109)

END


-- Execute this Procedure for following Method
--EXEC prDateCombination '15-Jan-2011'

Output :-