Creating a Calendar View

I like to put commonly used scripts into Views/Stored Procedures etc. It means I dont have to re-write out the logic each time, I know that there has been a consistent use of the logic and if there is a change needed to the logic I make it once rather than many times across all of my scripts/reports etc. Yeah seems obvious to do but Ive seen a lot of developers who dont bother.

Now this is view is related of course to my previous post Creating A Calendar Table but I find that having an independent view that you can use in Reports or reused SQL Scripts very helpful.

What better way to set default date values than referring to a consistent view AND if for some reason there is a change in regards to the definition of say the last day of the Previous Month - maybe it is now the last Monday for example instead of the last actual day you can change it within the view once and all of your reports/scripts are instantly updated. No more having to go through them all updating the logic.

Depending on where you are located you may want to change around a few of these values for example the Financial Years which wont apply to all.

-- =============================================

-- Create View template

-- =============================================

USE

ABC

-- Adjust accordingly

GO

IF

object_id(N'dbo.vDateDetails', 'V') IS NOT NULL

DROP VIEW dbo.vDateDetails

GO

CREATE

VIEW dbo.vDateDetails AS

SELECT

getdate

() AS [Now]

,

DATEADD(d, DATEDIFF(d,0,getdate()), 0) as Today

,

DATEADD(d, DATEDIFF(d,0,getdate()), -1) as Yesterday

,

CONVERT (varchar, getdate(), 112) As IntDate

,

DAY(getdate()) As [DayOfMonth]

,

DATEPART(dayofyear, getdate()) as [DayOfYear]

,

Month(getdate()) as MonthOfYear

,

DATENAME(month, getdate()) AS [MonthName]

,

YEAR(getdate()) as [Year]

,

case when Month(getdate()) > 6 then

right(cast(YEAR(getdate()) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)

end as CurrentFinancialYear

,

case when Month(getdate()) > 6 then

right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, -2, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, -1, getdate())) as varchar(4)), 2)

end as PreviousFinancialYear

,

case when Month(getdate()) > 6 then

right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 2, getdate())) as varchar(4)), 2)

else

right(cast(YEAR(dateadd(yy, 0, getdate())) as varchar(4)), 2) + '/' + right(cast(YEAR(dateadd(yy, 1, getdate())) as varchar(4)), 2)

end as NextFinancialYear

,

DATEPART(quarter, getdate()) as CalQuarter

,

DATEPART(week, getdate()) as WeekOfYear

,

DATEPART(weekday, getdate()) as [Weekday]

,

DATENAME(weekday, getdate()) AS [WeekDayName]

,

DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as MondayOfWeek

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate()),0)) as LastDayOfPreviousMonth

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) as LastDayOfCurrentMonth

,

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) as LastDayOfNextMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate()),0) as FirstDayOfCurrentMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate())+1,0) as FirstDayOfNextMonth

,

DATEADD(mm, DATEDIFF(m,0,getdate())-1,0) as FirstDayOfPreviousMonth

,

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as FirstDayOfCurrentQuarter

,

DATEADD(s,-1,DATEADD(qq, DATEDIFF(q,0,getdate())+1,0)) as LastDayOfCurrentQuarter