CODEWITHSUNDEEP
sql-serversql-server-2005date
Jay Cris Bondoc
Jay Cris Bondoc

Reputation: 11

How to count the number of days in a given month and in a given year in MSSQL

EmployeeID    RecordID         DateRecord
1               1         2/19/2013 12:00:00 AM
1               2         2/21/2013 12:00:00 AM
1               3         2/23/2013 12:00:00 AM
1               4         2/27/2013 12:00:00 AM
1               5         3/3/2013 12:00:00 AM
2               11        3/10/2013 12:00:00 AM
2               12        3/14/2013 12:00:00 AM
1               14        3/16/2013 12:00:00 AM

How can I count the number of days?

Example in February 2013 which has "19, 21, 23, 27" that should be count to "4" days .. ??

I found this method ..

SELECT DATEPART(yy, Daterecord),
   DATEPART(mm, Daterecord),
   DATEPART(dd, Daterecord),
   COUNT(*)

FROM Records

GROUP BY DATEPART(yy, Daterecord),
     DATEPART(mm, Daterecord),
     DATEPART(dd, Daterecord)

and resulted to ..

2013    2   19  1    
2013    2   21  1    
2013    2   23  1    
2013    2   27  1    
2013    3   3   1    
2013    3   10  1    
2013    3   14  1    
2013    3   16  1

it just get the specific dates but didm't count the total number of days in each month .. help me .. pls

Upvotes: 0

Views: 7221

Answers (6)

daemon
daemon

Reputation: 97

try this...

    declare @date2 nvarchar(max)
    set @date2 = (select getdate())
    select DateDiff(Day,@date2,DateAdd(month,1,@date2))

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35563

there is no 'yearmonth' in the suggested code ??

try this perhaps

select
           datename(month,daterecord) as [Month]
         , year(DateRecord)           as [Year]
         , count(distinct DateRecord ) as day_count
         , count(distinct dateadd(day, datediff(day,0, DateRecord ), 0)) as daytime_count
from your_table
where ( DateRecord >= '20130201' and DateRecord < '20130301' )
group by
           datename(month,daterecord)
         , year(DateRecord)

note the column [daytime_count] is only required if the field [DateRecord] has times othe than 12:00 AM (i.e. it "trims off" times so you deal with dates at 12:AM)

Regarding date range selections: many people will feel that using 'between' is the solution however that isn't true and the safest most reliable method is as I shown above. Note that the higher date is 1st March, but we are asking for information that is less than the 1st March, so we don't need to worry about leap years and we don't have to worry about hours and minutes either.

see: What do BETWEEN and the devil have in common?

Upvotes: 0

Suraj Singh
Suraj Singh

Reputation: 4059

I have change few names hopr you won't mind 

WITH Emp_CTE AS (

    SELECT EmployeeID ,DATEPART(yy, Daterecord) AS years,
       DATEPART(mm, Daterecord) AS months
      -- DATEPART(dd, Daterecord) AS days


    FROM testTrial
    )
    SELECT COUNT(months) AS noOfMonths ,* FROM Emp_CTE GROUP BY  months,EmployeeID,years

SqlFiddle

Upvotes: 1

Jazzy J
Jazzy J

Reputation: 311

Your initial query was almost right, just needed to remove the DATEPART(dd, Daterecord) from the grouping and it would work. Add in a HAVING clause to find the records from the month of February:

SELECT
    DATEPART(yy, Daterecord),
    DATEPART(mm, Daterecord),   
    COUNT(1)
FROM
    Records
GROUP BY
    DATEPART(yy, Daterecord),
    DATEPART(mm, Daterecord)
HAVING
    DATEPART(yy, eCreationTime) = 2013
AND DATEPART(mm, Daterecord) = 2

Upvotes: 0

Sean
Sean

Reputation: 1474

If your table is called Employee then this will do the trick:

select convert(varchar, DateRecord, 112)/ 100, count(*)
  from Employee
 group by convert(varchar, DateRecord, 112)/ 100

Upvotes: 0

kaushik0033
kaushik0033

Reputation: 677

Let you try this:-

1: Find the number of days in whatever month we're currently in

DECLARE @dt datetime
SET     @dt = getdate()

SELECT @dt AS [DateTime],
   DAY(DATEADD(mm, DATEDIFF(mm, -1, @dt), -1)) AS [Days in Month]Solution

2: Find the number of days in a given month-year combo

DECLARE @y int, @m int
SET     @y = 2012
SET     @m = 2

SELECT @y AS [Year],
   @m AS [Month],
   DATEDIFF(DAY,
            DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m - 1, 0)),
            DATEADD(DAY, 0, DATEADD(m, ((@y - 1900) * 12) + @m, 0))
       ) AS [Days in Month]

Upvotes: 0

Related Questions