Find Start Date, End Date and Number of Days in each from the Date Range given through SQL

One of my friend asked me how to get the Start Date, End Date and Number of Days in each from the Date Range given through SQL. To answer to his question I wrote the below SQL Query. Hope this is an useful one to share with you all.

DECLARE @StartDate DATETIME

DECLARE @EndDate DATETIME

SET @StartDate = ’06-01-2012′
SET @EndDate = ’06-01-2013′

IF @StartDate > @EndDate
BEGIN
PRINT ‘Friend! Please check your parameters’
END ;

WITH DateRange
AS (
SELECT @StartDate DateInfo

UNION ALL

SELECT dateadd ( MM, 1 , DateInfo ) DateInfo
FROM DateRange
WHERE DateInfo < @EndDate
)
SELECT
convert(varchar, DATEADD ( dd, – ( DAY( DateInfo ) – 1), DateInfo ), 101) as FirstDay,
convert(varchar, DATEADD( dd , – ( DAY (DATEADD ( mm, 1 , DateInfo ))), DATEADD ( mm, 1 , DateInfo)), 101) AS LastDay,
DAY(DATEADD(DD,-1,DATEADD(MM ,1,DATEADD(DD, 1 – DAY(DateInfo), DateInfo)))) as NoOfDays
FROM DateRange

Days

To find the number of weeks days in a month you can use the below query

This query referred from sqlservercentral

DECLARE @CurrentDate DATETIME

,@StartOfMonth DATETIME
,@EndofMonth DATETIME;

SELECT @CurrentDate = GETDATE();
SET @StartOfMonth = ’02-01-2013′;
SET @EndofMonth = ’02-28-2013′;

WITH MaxNumberOfDaysInAnyMonth (N) AS
(
SELECT 0
UNION ALL SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14
UNION ALL SELECT 15
UNION ALL SELECT 16
UNION ALL SELECT 17
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
UNION ALL SELECT 21
UNION ALL SELECT 22
UNION ALL SELECT 23
UNION ALL SELECT 24
UNION ALL SELECT 25
UNION ALL SELECT 26
UNION ALL SELECT 27
UNION ALL SELECT 28
UNION ALL SELECT 29
UNION ALL SELECT 30
),
DaysOfCurrentMonth AS
(
SELECT DATEADD(DD, N , @StartOfMonth ) DY
FROM MaxNumberOfDaysInAnyMonth
WHERE N <= DATEDIFF(DD,@StartOfMonth,@EndofMonth)
)
SELECT DATENAME(DW,DY) NameOfTheDay , COUNT(*) CountOfDays
FROM DaysOfCurrentMonth
GROUP BY DATENAME(DW,DY);

Daysinmonth

Advertisements

About Joseph Velliah
As a SharePoint Developer my professional interests tend to be technical and SharePoint focused. I run a blog at "SP RIDER" where you can expect to read HOW TOs and scenarios that I run into during my day to day job. I hope my posts will give back a little to the community that is helped me.

Comments are closed.

%d bloggers like this: