Solved

sql server - how to query specific time - T SQL

Posted on 2012-12-31
6
375 Views
Last Modified: 2012-12-31
I have a timestamp field.
I need to query all the records for the hour.. A record for 1,2,3 o'clock.
The data looks like

2012-11-01 01:00:00
2012-11-01 01:15:00
2012-11-01 01:45:00
2012-11-01 02:00:00
2012-11-01 02:15:00
2012-11-01 03:00:00

How do query for the full hour?
0
Comment
Question by:JElster
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38732995
This will give you the basic structure of the query:

select * from your_table
where column_name between convert(datetime, '2012-11-01 01:00:00', 120) and convert(datetime, '2012-11-01 02:00:00', 120)
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 38733050
Just so you understand, a TimeStamp field is not the same as a Date/Time field.  According to Microsoft:

"It is used as a mechanism for version-stamping table rows. The storage size is 8 bytes. The timestamp data type is just an incrementing number and does not preserve a date or a time. To record a date or time, use a datetime data type."
0
 
LVL 1

Author Comment

by:JElster
ID: 38733066
It's not a timestamp field... sorry for the confusion
Just a DATETIME
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38733240
Most important are:
1) not to perform any function on the column itself
2) not to count the same row more than once


I strongly recommend the style below for ALL date/datetime checks:

--query for 1AM rows only
WHERE
    datetime_column >= '20121101 01:00:00' AND
    datetime_column <  '20121101 02:00:00'


Leaving out the dashes prevents any conversion errors ('YYYYMMDD' is always interpreted correctly) and/or misunderstandings about what the date value represents.

Using >= and < rather than between avoids counting rows on the exact hour twice.
0
 
LVL 1

Author Comment

by:JElster
ID: 38733500
But I have many dates!



What about this...
First

SUBSTRING( CONVERT(nvarchar(30), TIMESTAMP, 120),15,5) AS DT

then

select ....   WHERE DT = '00:00'
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38733536
If you just want to group a lot of rows by their hours only, ignoring dates, do as below.

Yes, it uses a function on the column, but when you want all values you have to:


SELECT
    HOUR(TIMESTAMP) AS HR,
    ...
FROM dbo.tablename
--OPTIONAL!: for example, all records for current year only
--WHERE
    --TIMESTAMP >= '20120101' AND TIMESTAMP < '20130101'
GROUP BY
    HOUR(TIMESTAMP)
ORDER BY
    HOUR(TIMESTAMP)



If you want to see days, but only one row per hour per day, you can do this:

SELECT
    DATEADD(HOUR, DATEDIFF(HOUR, 0, TIMESTAMP), 0) AS DT,
    ...
FROM dbo.tablename
--OPTIONAL!: for example, all records for current year only
--WHERE
    --TIMESTAMP >= '20120101' AND TIMESTAMP < '20130101'
GROUP BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, TIMESTAMP), 0)
ORDER BY
    DATEADD(HOUR, DATEDIFF(HOUR, 0, TIMESTAMP), 0)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question