Get current month details in sql. Like, how many weeks in month?, Count of weekdays with there name?

Question

Today randomly one thought came, to make one dynamic query which get all the details of current month into one table, suppose if current month is May 2017, then the output table should be like this:

Details          |   Count
-----------------|------------
First Date       |  05-01-2017
Last Date        |  05-31-2017
Count of Mon     |  5
Count of Tues    |  5
Count of Wed     |  5
Count of Thur    |  4
Count of Fri     |  4
Count of Sat     |  4
Count of Sun     |  4

What did you try so far? Please make a [mcve], e.g. by representing your data in a sqlite `.dump` to play with or a different way of providing an environment for the readers. I do have ideas for SQLite by the way, if you tag it and provide mcve, I think I can edit your attempts tp solve this. — Yunnosch, May 09 '17 at 07:32

score 1 · Accepted Answer · edited May 23 '17 at 11:54

By combining the next Links:-

How can I select the first day of a month in SQL?

SQL Query to find the last day of the month

Count how many Sundays, Mondays, Tuesdays... in current month using MS SQL

Try the next code:-

declare @T table(Details varchar(100) , Result varchar(100));

WITH CTE AS
(
    SELECT DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE())[FIRST SUNDAY DATE],DATENAME(DW,DATEADD(D,-DATEPART(D,GETDATE())+1,GETDATE()))[DAY NAME]
    UNION ALL
    SELECT DATEADD(D,1,[FIRST SUNDAY DATE]),DATENAME(DW,DATEADD(D,1,[FIRST SUNDAY DATE]))FROM CTE WHERE [FIRST SUNDAY DATE]<=DATEADD(D,-DATEPART(D,GETDATE()),DATEADD(M,1,GETDATE()))-1
)
insert into @T(Details , Result)
SELECT [DAY NAME],COUNT([DAY NAME]) as 'COUNT' FROM CTE GROUP BY [DAY NAME]

Update @T
set Details = 'Count OF ' + Details

SELECT 'First Date' 'Details', convert(varchar(100),DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0),101) 'Count'
Union all
select 'Last Date' , convert(varchar(100),dateadd(month,1+datediff(month,0,GETDATE()),-1),101)
union all
select * from @T

Result:-

Amazing Ahmed, where else I was thinking to get first day of month and then increment it with 7days, but the way you made a calendar, it is superb. thanks to you and stackoverflow for giving the platform to meet such brilliant brains. — Jayank, May 10 '17 at 10:03
Hi Ahmed, could you help me on one more question https://stackoverflow.com/questions/44188006/manupilating-previous-month-data-according-to-current-month — Jayank, May 26 '17 at 14:22

score 0 · Answer 2 · answered May 09 '17 at 08:59

IF OBJECT_ID('Tempdb..#MonthDetails') IS NOT NULL
Drop Table #MonthDetails

Declare @monthdate DATE ='05-5-2017'--Provide date
SET @monthdate =CONVERT(VARCHAR(10), @monthdate, 105)

DECLARE @y INT = DATEPART(YEAR,@monthdate),
        @EndOFMonth Varchar(10),
        @StartOfMonth Varchar(10)

CREATE TABLE #MonthDetails(ID Int IDENTITY,[MonthName] Varchar(10),EndOFMonth Varchar(10),StartOfMonth Varchar(10)
,[Mon] INT,[Tue] INT,[Wed] INT,[Thu] INT, [Fri] INT,[Sat] INT,[Sun] INT,[Sum] INT)


SET @EndOFMonth= CONVERT(VARCHAR(10),EOMONTH(@monthdate),105) 
SET @StartOfMonth=CONVERT(VARCHAR(10),DATEADD(month, DATEDIFF(month, 0, @monthdate), 0),105) 

DECLARE @d DATETIME = dateadd(year, @y - 1900, 0)
;WITH CTE
AS
(
    SELECT 1 A, 
      LEFT(DATENAME(WEEKDAY, @D), 3) B,
      DATENAME(MONTH, 0) MONTH,
      1 SORT
    UNION ALL
    SELECT A + 1 A, 
      LEFT(DATENAME(WEEKDAY, @D + A), 3) B, 
      DATENAME(MONTH, @D + A) MONTH, 
      DATEPART(MONTH, @D + A) SORT
    FROM CTE WHERE A < DATEPART(DAYOFYEAR, DATEADD(YEAR, 1, @D)-1)
)
INSERT INTO #MonthDetails([MonthName],EndOFMonth,StartOfMonth,[Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun],[Sum])
SELECT  [MONTH],
      @EndOFMonth,
     @StartOfMonth,

     [Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun], 
     [Mon]+[Tue]+[Wed]+[Thu]+[Fri]+[Sat]+[Sun] [Sum] 
FROM cte
PIVOT (COUNT(a) FOR [b] IN ([Mon],[Tue],[Wed],[Thu],[Fri],[Sat],[Sun],[Sum])) AS pvt WHERE [MONTH]=DATENAME(MM,@monthdate)
ORDER BY sort 
OPTION (MAXRECURSION 366)

;With Result
AS
(

SELECT ISNULL(NULL,'FirstDate') AS Details ,StartOfMonth AS [Count]                 FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'LastDate') ,EndOFMonth                                          FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Mon')  , CAST([Mon] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Tues') , CAST([Tue] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Wed')  , CAST([Wed] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Thur') , CAST([Thu] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Fri')  , CAST([Fri] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Sat')  , CAST([Sat] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL
SELECT ISNULL(NULL,'Count of Sun')  , CAST([Sun] AS VARCHAR(5))                     FROM #MonthDetails  UNION ALL                 
SELECT ISNULL(NULL,'Total Days Of Month'),CAST([Sum]  AS VARCHAR(5))                FROM #MonthDetails
)
SELECT * from  Result

Output

Details                 |Count
-------------------------------------       
FirstDate               |01-01-2017
LastDate                |31-01-2017
Count of Mon            |5
Count of Tues           |5
Count of Wed            |4
Count of Thur           |4
Count of Fri            |4
Count of Sat            |4
Count of Sun            |5
Total Days Of Month     |31

Get current month details in sql. Like, how many weeks in month?, Count of weekdays with there name?

2 Answers2