SQL Server query - Count distinct DateTime field

  • In C#
  • Thursday, January 14, 2016
  • 3027 Views

SQL Server query - Count distinct DateTime field Group By Datetime field

Supposing we have the following records in an SQL Server table.

Date
19/5/2009 12:00:00 pm
19/5/2009 12:15:22 pm
20/5/2009 11:38:00 am

What is the SQL syntax for getting something like this one?

Date Count
19/5/2009 2
20/5/2009 1

 

Solution

You need to do any grouping on a Date only version of your datefield, such as this.

SELECT
	CONVERT(VARCHAR(10), YourDateColumn, 101),
	COUNT(*)
FROM
	YourTable
GROUP BY
	CONVERT(VARCHAR(10), YourDateColumn, 101)

I usually do this though, as it avoids conversion to varchar.

SELECT
	DATEPART(yy, YourDateColumn),
	DATEPART(mm, YourDateColumn),
	DATEPART(dd, YourDateColumn),
	COUNT(*)
FROM
	YourTable
GROUP BY
	DATEPART(yy, YourDateColumn),
	DATEPART(mm, YourDateColumn),
	DATEPART(dd, YourDateColumn)

EDIT: Another way to get just the date part of a datetime

DATEADD(d, 0, DATEDIFF(d, 0, YourDateColumn))

 

 

For Example

--

USE [YourDB]

GO

/******

 Object:  StoredProcedure [dbo].[Transaction_TotalCardNumber]    Script Date: 1/14/2016 3:12:30 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO


CREATE PROCEDURE [dbo].[Transaction_TotalCardNumber] 

@BeginDate DATETIME = NULL,

@EndDate DATETIME = NULL,

@Status INT = 0,

@TransactionType INT = 0

AS

BEGIN

;WITH O_Transaction AS(

SELECT  vt.[Id]

,vt.[CardNumber] -- 

,vt.[Type] TransactionType

,vt.[CreatedDate] -- Hình thức giao dịch: 1=tiêu điểm, 4=tích điểm

,COUNT(vt.[Id])   OVER()  AS TotalRow 

FROM [Transaction] vt WITH (NOLOCK)

WHERE (@BeginDate IS NULL OR vt.[CreatedDate] > @BeginDate)

AND (@EndDate IS NULL OR vt.[CreatedDate] <= @EndDate)

AND (@Status = 0 OR vt.[Status] = @Status

AND (vt.[Type] = 1 OR vt.[Type] = 4) -- chi lay giao dich tieu hoac tich

)

)

  

   SELECT DATEADD(d, 0, DATEDIFF(d, 0, [CreatedDate])) AS TransactionDate

    ,COUNT(distinct(CardNumber)) AS TotalCardNumber

   

   FROM O_Transaction

   WHERE (@TransactionType = 0 OR [TransactionType] = @TransactionType) 

   GROUP BY  DATEADD(d, 0, DATEDIFF(d, 0, [CreatedDate]))

   

   ORDER BY TransactionDate ASC

END


 

USE [YourDB]

GO

 

DECLARE @return_value int

-- tiêu điểm

EXEC @return_value = [dbo].[Transaction_TotalCardNumber]

@BeginDate = N'2015-12-25 00:00',

@EndDate = N'2016-01-14 18:00',

@Status = 0,

@TransactionType = 1

 

 

SELECT 'Return Value' = @return_value

 

GO