Retrieve dataset based on current time

Question

I have created a dataset within SSMS 2005, which contains a census datetime column (this is split by hours and minutes)

The dataset holds the last 7 days activity by each hour.

How can I create a WHERE clause to retrieve all records based on the current time, so for example when the query runs it should return all records where the TIME field is 15:00, as the current time now is 15:55?

hope that makes sense

Answer 1

Use DATEPART as:

DATEPART(HH,Datefield)

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 2

Can you post a sample of your dataset with column names and values?

There can be many solutions depending on your structure, say

HourColumn = datepart(hour, CURRENT_TIMESTAMP)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

-- sample table:
create table #testtable
(
    census datetime,
    [DateOnly] datetime,
    [TimeOnly] datetime,
	location varchar(50),
	[Dependency level] int
)
go
-- with some sample data:
insert into #testtable 
select '2012-07-11 01:00:00.000','2012-07-11 00:00:00.000','1900-01-01 01:00:00.000','CCA','3'union all  
select '2012-07-12 02:00:00.000','2012-07-12 00:00:00.000','1900-01-01 02:00:00.000','CCB','2'union all  
select '2012-07-13 03:00:00.000','2012-07-13 00:00:00.000','1900-01-01 03:00:00.000','CCB','3'union all  
select '2012-07-14 04:00:00.000','2012-07-14 00:00:00.000','1900-01-01 04:00:00.000','CCC','3'union all  
select '2012-07-15 05:00:00.000','2012-07-15 00:00:00.000','1900-01-01 05:00:00.000','CCD','3'union all  
select '2012-07-16 06:00:00.000','2012-07-16 00:00:00.000','1900-01-01 06:00:00.000','CCA','3'union all  
select '2012-07-17 07:00:00.000','2012-07-17 00:00:00.000','1900-01-01 07:00:00.000','CCB','2'union all  
select '2012-07-18 16:00:00.000','2012-07-18 00:00:00.000','1900-01-01 08:00:00.000','CCB','3'
select * from #testtable

Answer 4

For the above #testtable, since you already have the Census column with the DateTime, you can use it directly as:

SELECT * FROM #testtable
WHERE DATEPART(HH,Census) = DATEPART(HH,getdate())

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 5

Try then

select * from #testtable where TimeOnly = DATEADD(hour, datepart(hour, CURRENT_TIMESTAMP),'19000101')

This is a sargable expression.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

Also, If you would like to store only the date and time parts in separate columns, you might want to change the data types of the columns to VARCHAR.

Check out the table definition and insert statements below:

create table #testtable
(
    census datetime,
    [DateOnly] VARCHAR(25),
    [TimeOnly] VARCHAR(100),
	location varchar(50),
	[Dependency level] int
)
go
-- with some sample data:
insert into #testtable 
select '2012-07-11 01:00:00.000',CONVERT(VARCHAR(10),'2012-07-11 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-11 01:00:00.000' as DATETIME),114),'CCA','3'union all  
select '2012-07-12 02:00:00.000',CONVERT(VARCHAR(10),'2012-07-12 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-12 02:00:00.000' as DATETIME),114),'CCB','2'union all  
select '2012-07-13 03:00:00.000',CONVERT(VARCHAR(10),'2012-07-13 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-13 03:00:00.000' as DATETIME),114),'CCB','3'union all  
select '2012-07-14 04:00:00.000',CONVERT(VARCHAR(10),'2012-07-14 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-14 04:00:00.000' as DATETIME),114),'CCC','3'union all  
select '2012-07-15 05:00:00.000',CONVERT(VARCHAR(10),'2012-07-15 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-15 05:00:00.000' as DATETIME),114),'CCD','3'union all  
select '2012-07-16 06:00:00.000',CONVERT(VARCHAR(10),'2012-07-16 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-16 06:00:00.000' as DATETIME),114),'CCA','3'union all  
select '2012-07-17 07:00:00.000',CONVERT(VARCHAR(10),'2012-07-17 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-17 07:00:00.000' as DATETIME),114),'CCB','2'union all  
select '2012-07-18 16:00:00.000',CONVERT(VARCHAR(10),'2012-07-18 00:00:00.000',101),CONVERT(VARCHAR(20),CAST('2012-07-18 16:00:00.000' as DATETIME),114),'CCB','3'




SELECT * FROM #testtable





SELECT * FROM #TestTable
WHERE DATEPART(HH,GETDATE()) = DATEPART(HH,TimeOnly)

And if you do that you can use the following query to return the data the way you wanted to:

SELECT * FROM #TestTable
WHERE DATEPART(HH,GETDATE()) = DATEPART(HH,TimeOnly)

---

Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

Answer 7

This is perfect

WHERE DATEPART(HH,GETDATE()) = DATEPART(HH,TimeOnly)

but i noticed when running this query at 08:58 this morning and there wasn't a record for 08:00 in the dataset, is there any way it would then do another search and bring the next closet time possible from the dataset??

meaning if a record TIMED for 08:00 wasn't present, it should search for 07:00, then 06:00 etc... until it find a record!!

Answer 8

Do you have times without minutes and seconds? If yes, then my solution is better as it's a sargable solution and don't use function on a column.

To get the top 1 closest to the date you need to use

select top (1) ... from .... where TimeOnly <= dateadd(hour, datediff(hour, '19000101', CURRENT_TIMESTAMP),'19000101')

order by date DESC

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog