CODEWITHSUNDEEP
sql-server-2008t-sqldatetime
Enrico
Enrico

Reputation: 2037

T-SQL Select data from multiple days between certain times

In my table I have minute data about daily temperature measurements. The table looks like:

DateTime timestamp, Float temperature

I would like to have the temperatures on different dates between a certain interval and then only show the temperature between 7am and 8pm.

I know how to get the data between dates:

SELECT [timestamp],[temperature]
  FROM [meteo_data]
  WHERE [timestamp] BETWEEN '2012-11-10' and '2012-11-17'

How to I implement the time restriction (7am - 8pm) as well?

Thanks a lot!!

Upvotes: 2

Views: 4360

Answers (2)

Kaf
Kaf

Reputation: 33809

If you are on SQL Server 2008 or above, you can use TIME datatype

SELECT [timestamp],[temperature]
FROM [meteo_data]  
WHERE [timestamp] BETWEEN '2012-11-10' and '2012-11-17'
AND CONVERT(TIME,[timestamp]) BETWEEN '19:00:00' AND '20:00:00'

EDIT: Also it is recommended to use ISO (yyyymmdd) date format when using date as a string. i.e.

BETWEEN '20121110' and '20121117'

Upvotes: 5

Simon Martin
Simon Martin

Reputation: 4231

The DateTime and TimeStamp values should contain precision you need, so 

SELECT [timestamp],[temperature]
FROM [meteo_data]  
WHERE [timestamp] BETWEEN '2012-11-10' and '2012-11-17'
AND RIGHT([timestamp], 12) BETWEEN '19:00:00.000' AND '20:00.00.000'

You may need to adjust how many characters you are evaluating in the RIGHT predicate depending on the precision in your database. But the idea is to take all the parts that constitute the hours, minutes, seconds and miliseconds and restrict that to just those between the hours you require.

Upvotes: 1

Related Questions