quote:

---

Originally posted by emmim44

Hi all,
I need to get records between specific time date...
Example: if now is "27.06.2012 16:16:00"...I need get record count between "27.06.2012 15:00:00" and "27.06.2012 16:00:00". Any help will be appreciated... I need to keep the same datetime format though..

---

SELECT
	COUNT(*)
FROM
	YourTable
WHERE
	yourDateColumn >=DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0)
	AND yourDateColumn < DATEADD(hour,DATEDIFF(hour,0,GETDATE())+1,0)

quote:

---

Originally posted by sunitabeck

quote:

---

Originally posted by emmim44

Hi all,
I need to get records between specific time date...
Example: if now is "27.06.2012 16:16:00"...I need get record count between "27.06.2012 15:00:00" and "27.06.2012 16:00:00". Any help will be appreciated... I need to keep the same datetime format though..

---

Your query would be something like shown below assuming that the data type of the "yourDateColumn" is a datetime type.

SELECT
	COUNT(*)
FROM
	YourTable
WHERE
	yourDateColumn >=DATEADD(hour,DATEDIFF(hour,0,GETDATE()),0)
	AND yourDateColumn < DATEADD(hour,DATEDIFF(hour,0,GETDATE())+1,0)

However, I didn't follow what you meant by "I need to keep the same datetime format though"

---

quote:

---

Originally posted by sunitabeck

The query I posted looks for the records within the same hour as the current time (which was what I understood from your original post). For example, it is 7:42 in the morning when I am typing this, so it would return all the records between 7:00 and 8:00 AM if I were to run the query now.

If you want to query for the one hour window around another time, replace the two instances of GETDATE() with that time.

---

CREATE TABLE #tmp (DateCol DATETIME);

INSERT INTO #tmp VALUES
('2012-06-28T08:04:49.600'),
('2012-06-28T05:03:42.600'),
('2012-06-28T05:33:51.000'),
('2012-06-28T05:28:11.000')

SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28T05:30:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28T05:30:00.000')+1,0)
	
DROP TABLE #tmp;

quote:

---

Originally posted by sunitabeck

Can you post some sample data from your table and describe the output you are expecting? Shown below is an example I constructed. In the query I am looking for records with the same hour as '2012-06-28T05:30:00.000' - i.e., between 5:00 AM and 6:00 AM on June 28, 2012.

CREATE TABLE #tmp (DateCol DATETIME);

INSERT INTO #tmp VALUES
('2012-06-28T08:04:49.600'),
('2012-06-28T05:03:42.600'),
('2012-06-28T05:33:51.000'),
('2012-06-28T05:28:11.000')

SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28T05:30:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28T05:30:00.000')+1,0)
	
DROP TABLE #tmp;

I get the result as 3 as expected.

---

quote:

---

My sample data:

TSTIDM_log_Entry_Date
2012-06-23 18:45:04.000
2012-06-23 04:05:02.000
2012-06-28 04:20:04.000
2012-06-27 08:50:15.000
2012-06-26 17:30:13.000
2012-06-28 12:00:34.000
2012-06-28 02:45:03.000
2012-06-27 19:55:01.000
2012-06-28 08:00:36.000
2012-06-26 22:20:14.000
2012-06-22 16:20:01.000
2012-06-24 20:20:09.000
2012-06-27 00:50:14.000
2012-06-22 18:45:04.000
2012-06-24 02:50:06.000
2012-06-22 20:00:33.000
2012-06-25 22:15:11.000

---

quote:

---

Originally posted by sunitabeck

quote:

---

My sample data:

TSTIDM_log_Entry_Date
2012-06-23 18:45:04.000
2012-06-23 04:05:02.000
2012-06-28 04:20:04.000
2012-06-27 08:50:15.000
2012-06-26 17:30:13.000
2012-06-28 12:00:34.000
2012-06-28 02:45:03.000
2012-06-27 19:55:01.000
2012-06-28 08:00:36.000
2012-06-26 22:20:14.000
2012-06-22 16:20:01.000
2012-06-24 20:20:09.000
2012-06-27 00:50:14.000
2012-06-22 18:45:04.000
2012-06-24 02:50:06.000
2012-06-22 20:00:33.000
2012-06-25 22:15:11.000

---

If you are looking for the count in this sample data "from: 2012-06-28 13:00:00.000 TO 2012-06-28 14:00:00.000...." as you indicated earlier, it would/should return 0

---

CREATE TABLE #tmp (DateCol DATETIME);

--- insert data that is outside of the range of interest.
INSERT INTO #tmp VALUES
('2012-06-23 18:45:04.000'),
('2012-06-23 04:05:02.000'),
('2012-06-28 04:20:04.000'),
('2012-06-27 08:50:15.000'),
('2012-06-26 17:30:13.000'),
('2012-06-28 12:00:34.000'),
('2012-06-28 02:45:03.000'),
('2012-06-27 19:55:01.000'),
('2012-06-28 08:00:36.000'),
('2012-06-26 22:20:14.000'),
('2012-06-22 16:20:01.000'),
('2012-06-24 20:20:09.000'),
('2012-06-27 00:50:14.000'),
('2012-06-22 18:45:04.000'),
('2012-06-24 02:50:06.000'),
('2012-06-22 20:00:33.000'),
('2012-06-25 22:15:11.000');

-- verify that the result returned is 0.
SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000')+1,0)

-- insert two rows that are within the range.
INSERT INTO #tmp VALUES
('2012-06-28 13:45:00.000'),
('2012-06-28 13:58:00.000');

-- verify that the query returns 2.
SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000')+1,0)

DROP TABLE #tmp;

quote:

---

Originally posted by sunitabeck

I don't know what might be causing the query to return incorrect results when you run it. The only thing I could think of was if the data type of your column is not of a datetime type. But you have already confirmed that it is of type DATETIME.

I ran the the query I had posted earlier with the sample data that you posted, and it works as I expect it would.

CREATE TABLE #tmp (DateCol DATETIME);

--- insert data that is outside of the range of interest.
INSERT INTO #tmp VALUES
('2012-06-23 18:45:04.000'),
('2012-06-23 04:05:02.000'),
('2012-06-28 04:20:04.000'),
('2012-06-27 08:50:15.000'),
('2012-06-26 17:30:13.000'),
('2012-06-28 12:00:34.000'),
('2012-06-28 02:45:03.000'),
('2012-06-27 19:55:01.000'),
('2012-06-28 08:00:36.000'),
('2012-06-26 22:20:14.000'),
('2012-06-22 16:20:01.000'),
('2012-06-24 20:20:09.000'),
('2012-06-27 00:50:14.000'),
('2012-06-22 18:45:04.000'),
('2012-06-24 02:50:06.000'),
('2012-06-22 20:00:33.000'),
('2012-06-25 22:15:11.000');

-- verify that the result returned is 0.
SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000')+1,0)

-- insert two rows that are within the range.
INSERT INTO #tmp VALUES
('2012-06-28 13:45:00.000'),
('2012-06-28 13:58:00.000');

-- verify that the query returns 2.
SELECT
	COUNT(*)
FROM
	#tmp
WHERE
	DateCol >=DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000'),0)
	AND DateCol < DATEADD(hour,DATEDIFF(hour,0,'2012-06-28 13:00:00.000')+1,0)

DROP TABLE #tmp;

Figuring out why it does not work for you is beyond my skills and level of knowledge. Hopefully some of the experts on the forum would chime in.

---

quote:

---

Thank you very much for your help. I found what caused not getting the correct number of records. Apparently, our SQL 08 saves that field's date as UTC...And our front end application shows as local datetime...That was the confusion...Simpy replace getDate() with getUTCDATE resolved the issue..

---