Useful scripts for date functions

To get last completed month's startdate and enddate and its fiscalmonth, fiscalquarter and fiscalyear

SELECT ? = DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)

SELECT ? = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

DECLARE @DATE DATETIME, @Quarter INT

SET @DATE = (convert(varchar(20),(SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)),111))

SET @Quarter = Datepart(QQ,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0))

SELECT ? = CASE WHEN (MONTH(@Date) > 9) THEN YEAR(@Date) + 1

ELSE YEAR(@Date) END -- AS FiscalYear

SELECT ?= CASE WHEN (MONTH(@Date) > 9) THEN MONTH(@Date) - 9

ELSE MONTH(@Date) + 3 END -- AS FiscalMonth

SELECT ?= (SELECT DISTINCT FiscalQuarter FROM DimDate WHERE QuarterNumber = @Quarter)

To get last completed quarter's startdate and enddate and its ficalquarter and fiscalmonth

DECLARE @Date DATETIME

,@StartOfQuarter DATETIME

,@EndOfQuarter DATETIME

,@FiscalYear INT

,@FiscalQuarter INT

SET @Date = GETDATE()

SELECT @FiscalYear = FiscalYear, @FiscalQuarter = FiscalQuarter FROM DimDate

WHERE CONVERT(DATE, DateValue) = CONVERT(DATE,@Date)

--SELECT @FiscalYear, @FiscalQuarter

IF @FiscalYear = YEAR(@Date)

BEGIN

SET @FiscalYear = @FiscalYear

SET @FiscalQuarter = @FiscalQuarter - 1

--Print @FiscalYear

END

ELSE

BEGIN

IF(@FiscalQuarter = 1)

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @FiscalQuarter = 4

END

ELSE

BEGIN

SET @FiscalYear = @FiscalYear -1

SET @FiscalQuarter = @FiscalQuarter

END

END

SELECT ? = @FiscalYear

SELECT ? = @FiscalQuarter

SELECT @StartOfQuarter = (SELECT Min(datevalue) from dimdate where fiscalyear = @FiscalYear AND

fiscalquarter = @FiscalQuarter)

SELECT ? = @StartOfQuarter

SELECT @EndOfQuarter = (SELECT Max(datevalue) from dimdate where fiscalyear = @FiscalYear AND

fiscalquarter = @FiscalQuarter)

SELECT ? = @EndOfQuarter

To get FYTD's startdate and endate and its fiscalmonth, fiscalquarter and fiscalyear

DECLARE @Date DATETIME

,@FYTDStartDate DATETIME

,@FYTDEndDate DATETIME

,@FiscalYear INT ,@FiscalQuarter INT ,@Year INT

,@Month INT, @FiscalMonth INT

,@PreviousMonth DATETIME

SET @PreviousMonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))

SET @Date = GETDATE()

SELECT @FiscalYear = fiscalyear, @Month = fiscalmonth, @FiscalQuarter = FiscalQuarter FROM dimdate

WHERE CONVERT(DATE, datevalue) = CONVERT(DATE,@Date)

SELECT @FiscalQuarter = FiscalQuarter FROM dimdate

WHERE CONVERT(DATE, datevalue) = CONVERT(DATE,@PreviousMonth)

SET @FiscalMonth = @Month

SET @Year = @FiscalYear

IF @FiscalYear = YEAR(@Date)

BEGIN

SET @FiscalYear = @FiscalYear

SET @Year = @FiscalYear

SET @Month = 1

SET @FiscalMonth = @FiscalMonth-1

END

ELSE

BEGIN

IF(@Month = 1)

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @Year = @FiscalYear

SET @Month = 1

SET @FiscalMonth = 12

END

ELSE

BEGIN

SET @FiscalYear = @FiscalYear-1

SET @Year = @FiscalYear+1

SET @Month = 1

SET @FiscalMonth = @FiscalMonth-1

END

END

SELECT ?= @Year

SELECT ?= @FiscalMonth

SELECT ?= @FiscalQuarter

SELECT @FYTDStartDate = (SELECT Min(datevalue) from dimdate where

(fiscalyear >= @Year AND fiscalyear <= @Year) AND

(fiscalmonth >= @Month AND fiscalmonth <= @FiscalMonth))

SELECT ?= @FYTDStartDate

SELECT @FYTDEndDate = (SELECT Max(datevalue) from dimdate where

(fiscalyear >= @FiscalYear AND fiscalyear <= @Year) AND

(fiscalmonth >= @Month AND fiscalmonth <= @FiscalMonth))

SELECT ?= @FYTDEndDate

Note: we need DimDate table