CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Sammy
Sammy

Reputation: 808

How to count number of records present in a date range between a fixed time in SQL Server?

I need to get the number of records present in my [RecordsTable] for the last 3 months. However the catch is I need the records which are processed between 10PM and 2AM.

For example --

07/01/2015 10PM -- 07/02/2015 2AM 
07/02/2015 10PM -- 07/03/2015 2AM
07/03/2015 10PM -- 07/04/2015 2AM

The below SQL gives me the records present on any particular day starting from May,2015.

But I am not able to get the timing(10PM-2AM of next day) embedded in the SQL and need some help.

SELECT CONVERT(VARCHAR(12), RecordDate, 101),count(RecordID)
FROM [RecordsTable](NOLOCK)
WHERE RecordDate > '2015-05-01'
GROUP BY CONVERT(VARCHAR(12), RecordDate, 101)

Upvotes: 1

Views: 137

Answers (5)

Philip Kelley
Philip Kelley

Reputation: 40309

Try the following:

SELECT count(1)
FROM RecordsTable
WHERE RecordDate > '2015-05-01'
 AND NOT DATEPART(hour, RecordDate) BETWEEN 2 AND 21

I assume RecordDate is a datetime or datetime2 column. between 2 and 21 will return rows where the hour for RecordDate is between 2am and 9pm, inclusive. NOT between 2 and 21 will return the reverse, giving you data for 10pm, 11pm, 12pm, and 1am. This does not include any time between 2:00am and 2:59am. If you need to include events that occurred precisely at but not after 2:00am, things get a bit tricker, but similar code based on not between would apply.

Upvotes: 1

nishantn3
nishantn3

Reputation: 1

Try this

SELECT count(*) FROM tablename where created_at>='2015-03-17 07:15:10' and created_at<='2015-07-09 02:23:50';

You can even use between 

SELECT count(*) FROM tablename where created_at between '2015-03-17 07:15:10' and '2015-07-09 02:26:50';

You can use curdate() to get today's date

Upvotes: -1

jhilden
jhilden

Reputation: 12429

Use DATEPART

SELECT COUNT(1)
FROM Table1
WHERE RecordDate > '2015-05-01'
   AND (DATEPART(HOUR, RecordDate) <= 2 OR DATEPART(HOUR, RecordDate) >= 22)

Upvotes: 0

Hogan
Hogan

Reputation: 70523

To get records in the last 3 months you can use two ways -- one by month looks like this

WHERE MONTH(colname) >= MONTH(GETDATE()) -3

This will get you inclusive months but not partial months. To get partial months is a bit more tricky because you could mean (for example for today) the 9th day of 3 months ago or you could mean 90 days ago. In the first case this works

WHERE colname >= dateadd(month,-3, getdate())

and for 90 days ago

WHERE colname >= dateadd(day,-90, getdate())

To get between 10PM and 2AM use this

WHERE datepart(hour,colname) >= 22 OR datepart(hour,colname) <= 2

Upvotes: 0

Brad D
Brad D

Reputation: 762

MSSQL Supports both Date and Time datatypes. You can break up your where statement to reflect both date and time conditions separately.

SELECT COUNT(Records)
FROM TABLE
WHERE CONVERT(Date,DateCol) BETWEEN 'MM/DD/YYYY' AND 'MM/DD/YYYY'
AND CONVERT(Time,DateCol) BETWEEN 'HH:MM:SS' AND 'HH:MM:SS'

Upvotes: 1

Related Questions