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 

Query to get first day and last day for all the periods

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()

--Today
SELECT @mydate 'Today'

--Yesterday
SELECT DATEADD(d,-1,@mydate()) 'Yesterday'

--First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,@mydate),0) 'First Day of Current Week'

--Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,@mydate),6) 'Last Day of Current Week'

--First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,@mydate),0) 'First Day of Last Week'

--Last Day of Last Week

SELECT DATEADD(wk,DATEDIFF(wk,7,@mydate),6) 'Last Day of Last Week'

--Last Day of Previous Month  --can also use <SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0))>
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101) 

--First Day of Previous Month  --can also use <SELECT DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)>
SELECT CONVERT(VARCHAR(25),DATEADD(mm, DATEDIFF(m,0,@mydate)-1,0),101) 

--First Day of Current Month 
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value

--Last Day of Current Month  --Can also use <SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))>
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101) 

--First Day of Next Month
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101) 

--First Day of Current Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(qq,DATEDIFF(qq,0,@mydate),0),101)

--Last Day of Current Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(qq,DATEDIFF(qq,-1,@mydate),-1),101)

--First Day of Next Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(qq, DATEDIFF(qq, 0, @mydate) + 1, 0),101)

--Last Day of Next Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(s,-1,DATEADD(qq,DATEDIFF(qq,0,@mydate)+2,0)),101)

--Last Day of Next Month   --Can also use <SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0))>
SELECT CONVERT(VARCHAR(25),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@mydate)+2,0)),101)

--Last Day of a month before the lastmonth
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-1,DATEADD(mm, DATEDIFF(m,0,@mydate)-1,0)),101)

--First Day of a quarter before the last completed quarter
SELECT CONVERT(VARCHAR(25),DATEADD(DD,-1,DATEADD(qq,DATEDIFF(qq,0,@mydate)-1,0)),101)

--First Day of Previous Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(qq,DATEDIFF(qq,0,GETDATE())-1,0),101)

--Last Day of Previous Quarter
SELECT CONVERT(VARCHAR(25),DATEADD(DAY, -1, DATEADD(QQ, DATEDIFF(QQ, 0, GETDATE()), 0)),101)

--QuarterPart from a Date
SELECT CONVERT(VARCHAR(25),DATENAME(QUARTER,(DATEADD(DD,-1,DATEADD(qq,DATEDIFF(qq,0,@mydate)-1,0)))),101)

--YearPart from a Date
SELECT CONVERT(VARCHAR(25),YEAR(DATEADD(DD,-1,DATEADD(qq,DATEDIFF(qq,0,@mydate)-1,0))),101)

--MonthPart from a Date
SELECT CONVERT(VARCHAR(25),MONTH(DATEADD(DD,-1,DATEADD(qq,DATEDIFF(qq,0,@mydate)-1,0))),101)

--First Day of Last Year

SELECT DATEADD(yy,-1,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0))'

--Last Day of Last Year 
SELECTDATEADD(ms,-3,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)))

Date Dimension Table Script

Script to create a Date Dimension table which is default to most of the data warehouse development

USE [xxxxx] --Database name

GO

DECLARE @dtStartDate DATETIME
DECLARE @dtEndDate DATETIME
DECLARE @dtCurrDate DATETIME

SELECT @dtStartDate = '1/1/2012'
SELECT @dtEndDate = '12/31/2012'

SELECT @dtCurrDate=NULL

SELECT @dtCurrDate = ISNULL (@dtCurrDate, @dtStartDate)
Declare @datedim TABLE
(
Datekey DATETIME
,DateWithDayName NVARCHAR(500)
,CurrYear INT
,CalYear NVARCHAR(100)
,FirstDayOfQtr DATETIME
,QTYYear NVARCHAR(250)
,FirstDayOfMonth DATETIME
,MonthYear NVARCHAR(500)
,DayOfTheYear INT
,DayOfTheYearname NVARCHAR(50)
,Day_of_the_quarter INT
,Day_of_the_quarter_name NVARCHAR(200)
,Day_of_month INT
,Day_of_month_name NVARCHAR(250)
,Month_of_year INT
,Month_of_year_name NVARCHAR(250)
,Month_of_quarter INT
,Month_of_quarter_name NVARCHAR(250)
,Quarter_in_year INT
,Quarter_in_year_name NVARCHAR(205)
,MMDDYYYY NVARCHAR(10)
,DDMMYYYY NVARCHAR(10)
,YYYYDDMM NVARCHAR(10)
,Semester_in_year int
,Semester_in_year_name NVARCHAR(15)
,Month_of_semester int
,Month_of_semester_name NVARCHAR(25)
,Day_of_the_semester int
,Day_of_the_semester_name NVARCHAR(30)
)

WHILE @dtCurrDate <= @dtEndDate
BEGIN
INSERT INTO @datedim
(datekey
,datewithdayname
,curryear
,calyear
,FirstDayOfQtr
,QTYYear
,FirstDayOfMonth
,MonthYear
,DayOfTheYear
,DayOfTheYearname
,day_of_the_quarter
,day_of_the_quarter_name
,day_of_month
,day_of_month_name
,Month_of_year
,Month_of_year_name
,Month_of_quarter
,Month_of_quarter_name
,quarter_in_year
,quarter_in_year_name
,MMDDYYYY
,DDMMYYYY
,YYYYDDMM
,semester_in_year
,semester_in_year_name
,month_of_semester
,month_of_semester_name
,day_of_the_semester
,day_of_the_semester_name
)
SELECT @dtCurrDate AS datekey
,DATENAME(DW, @dtCurrDate)+', '+DATENAME(month, @dtCurrDate)
+ ' ' +CONVERT(NVARCHAR (2) , DAY(@dtCurrDate)) + ' ' +Convert (NVARCHAR(4),YEAR(@dtCurrDate) ) as datewithdayname
,YEAR(@dtCurrDate) as curryear
, ' Calendar'+Convert (NVARCHAR(4),YEAR(@dtCurrDate) ) as calyear
,CASE WHEN DATEPART(qq, @dtCurrDate) IN (1,2,3) THEN Convert(DATETIME,'01/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (4,5,6) THEN Convert(DATETIME,'04/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (7,8,9) THEN Convert(DATETIME,'07/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
WHEN DATEPART(qq, @dtCurrDate) IN (10,11,12) THEN Convert(DATETIME,'10/01/'+ CONVERT(varchar(4),YEAR(@dtCurrDate)))
END as FirstDayOfQtr
,'Quarter '+ convert (NVARCHAR,DATEPART(qq, @dtCurrDate))+','+ convert (NVARCHAR,YEAR(@dtCurrDate)) as QTYYear
,CONVERT(DATETIME,Convert(NVARCHAR(2),Month(@dtCurrDate))+'/01/'+Convert(NVARCHAR(4),Year(@dtCurrDate))) as FirstDayOfMonth
,DATENAME(month,@dtCurrDate)+Convert(NVARCHAR(4),Year(@dtCurrDate)) as MonthYear
,DATEPART(DAYOFYEAR,@dtCurrDate) as DayOfTheYear
,'Day '+Convert(NVARCHAR(3),DATEPART(DAYOFYEAR,@dtCurrDate)) as DayOfTheYearname
--day of the quarter
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (4,5,6) THEN DATEDIFF(DD,'4/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (7,8,9) THEN DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (10,11,12) THEN DATEDIFF(DD,'10/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
END as day_of_the_quarter
--day of the quarter name
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (4,5,6) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'4/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (7,8,9) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
WHEN Month( @dtCurrDate) IN (10,11,12) THEN 'Day '+Convert(NVARCHAR,DATEDIFF(DD,'10/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1)
END as day_of_the_quarter_name
,Day(@dtCurrDate) as day_of_month
,'Day '+CONVERT(NVARCHAR,Day(@dtCurrDate)) as day_of_month_name

,Month(@dtCurrDate) as Month_of_year
,'Month '+CONVERT(NVARCHAR,Month(@dtCurrDate)) as Month_of_year_name
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN MONTH(@dtCurrDate)
WHEN Month( @dtCurrDate) IN (4,5,6) THEN MONTH(@dtCurrDate)-4+1
WHEN Month( @dtCurrDate) IN (7,8,9) THEN MONTH(@dtCurrDate)-7+1
WHEN Month( @dtCurrDate) IN (10,11,12) THEN MONTH(@dtCurrDate)-10+1
END as month_of_quarter
,CASE WHEN Month( @dtCurrDate) IN (1,2,3) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate))
WHEN Month( @dtCurrDate) IN (4,5,6) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-4+1)
WHEN Month( @dtCurrDate) IN (7,8,9) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-7+1)
WHEN Month( @dtCurrDate) IN (10,11,12) THEN 'Month '+CONVERT(NVARCHAR, MONTH(@dtCurrDate)-10+1)
END as month_of_quarter_name

,DATEPART(qq, @dtCurrDate) as quarter_in_year
,'Quarter '+Convert(NVARCHAR,DATEPART(qq, @dtCurrDate)) as quarter_in_year_name
,CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
+
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
as MMDDYYYY
,
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
+
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
as DDMMYYYY
,
CASE
WHEN year(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,year(@dtCurrDate))
else CONVERT(NVARCHAR,year(@dtCurrDate))
end
+
CASE
WHEN day(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,day(@dtCurrDate))
else CONVERT(NVARCHAR,day(@dtCurrDate))
end
+
CASE
WHEN Month(@dtCurrDate)<10 THEN '0'+CONVERT(NVARCHAR,Month(@dtCurrDate))
else CONVERT(NVARCHAR,Month(@dtCurrDate))
end
as YYYYDDMM

, CASE WHEN Month(@dtCurrDate) BETWEEN 1 AND 6 THEN 1 ELSE 2 END AS semester_in_year
,'Semester '+ CASE WHEN Month(@dtCurrDate) BETWEEN 1 AND 6 THEN '1' ELSE '2' END AS semester_in_year
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN MONTH(@dtCurrDate)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN MONTH(@dtCurrDate)-7+1
END as month_of_semester
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN 'Month ' + Cast(MONTH(@dtCurrDate) AS nvarchar)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN 'Month ' + CAST(MONTH(@dtCurrDate)-7+1 AS nvarchar)
END as month_of_semester_name
--day of the semester
,CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1
END as day_of_the_semester
,'Day '+CASE WHEN Month( @dtCurrDate) IN (1,2,3,4,5,6) THEN Cast(DATEDIFF(DD,'1/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1 as nvarchar)
WHEN Month( @dtCurrDate) IN (7,8,9,10,11,12) THEN Cast(DATEDIFF(DD,'7/1/'+Convert(NVARCHAR,YEAR(@dtCurrDate)),@dtCurrDate)+1 as nvarchar)
END as day_of_the_semester_name

SELECT @dtCurrDate = DATEADD(d, 1, @dtCurrDate)
END

SELECT * INTO DimDate FROM @datedim