SQL Query for time data only
I need to query a large remote weather database for events reported within time frames without regard for the day. For instance, I need all data that was reported for the fifteen minute window closest to noon each day for the last twelve years. I don't care about specific dates, just the daily time windows. How do I write this query to download only one record for each of thousands of days?
Thanx,
Pete
Software/Hardware used:
Software/Hardware used:
ASKED:
April 17, 2008 7:29 PM
UPDATED:
April 18, 2008 7:00 PM
Answer Wiki:
Assuming that your column has a data type of datetime you'll need to use a CONVERT statement in your WHERE clause to find the data you are looking for.
This query will return all the values where the time is within 5 minutes after noon, or the highest value before noon.
<pre>
SELECT *
FROM Table
JOIN (SELECT IdColumn, convert(varchar(10), YourColumn, 101) DateColumn, max(YourColumn)
FROM Table
WHERE CONVERT(VARCHAR(10), YourColumn, 108) > '12:05:00 PM'
GROUP BY idColumn, convert(varchar(10), YourColumn, 101)
) t1 ON TAble.IdColumn = t1.IdColumn
</pre>
This query will be very slow to run as you will not be able to use an index to search the table. An index scan or table scan will be required because you are doing a convert against the column in the table.
This example assumes that the date and time are stored in a single field.
To see all answers submitted to the Answer Wiki: View Answer History.
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Thanks, Mrdenny, but that’s not quite there yet. The situation is that the government NOAA has a database somewhere in the universe that logs data from hundreds of stations all over the world at random intervals. There may be one or ten entries for temperatures between 11:30 am and 12:30 pm on any particular day. What I need is to ask for the records closest to 12:00 pm for every day for the last several years. I’ll then be able to use Excel to figure an average noontime temperature for whatever calendar period. My problem is that the date and time data is stored as one number so I can’t just say “WHERE time between hh:mm:ss AND hh:mm:ss. It doesn’t seem to be able to figure the time without the date too and I can’t type in a date for each day in the decade. I forgot to mention, this isn’t a programming task; it’s just a manual query type in from the keyboard whenever someone asks for the info – so no programming examples, please!
Any query examples would be able to be used as one off queries to dump the data so it can be loaded into excel.
The only way to get this data out will be with a bit of SQL code.
I assume that the database is running SQL Server? If it’s running a different database platform that will change the query a bit.