|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Searching a datetime field by timeI have the time of an event stored on each record as a datetime field.It
includes the year,month,day, etc. Suppose my user wants to search the table for all events over the lunch hour, say between11am and 1pm. How do I construct the SELECT query to peek into each datetime field and return only those records that satify the specified time range? Many thanks. Thanks to you and Barry for quick replies -- and two approaches.
- Andrew Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1137094156.880496.118970@z14g2000cwz.googlegroups.com... > Use the datepart function > > where datepart(hh,DateField) in (11,12) > > http://sqlservercode.blogspot.com/ > Andrew,
You could use something like this: Select * From MyTable Where DateTimeColumn > Convert(Datetime, '2006-01-12 11:30') And DateTimeColumn < Convert(Datetime, '2006-01-12 14:30') HTH Barry If you are searching just one day then use Barry's method since it will
be faster If you need to search for a period that is longer than 1 day then use my solution http://sqlservercode.blogspot.com/ Or something that might be faster (you'll have to experiment):
SELECT DateColumn FROM ( SELECT DateColumn, DateColumnCalc = DateColumn - DATEDIFF(DAY, '19000101', DateColumn) FROM table ) x WHERE DateColumnCalc >= '11:00' AND DateColumnCalc < '13:00'; Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1137095323.912990.7040@g43g2000cwa.googlegroups.com... > If you are searching just one day then use Barry's method since it will > be faster > If you need to search for a period that is longer than 1 day then use > my solution > > > http://sqlservercode.blogspot.com/ > I am actually searching over several days. One other problem that I omitted
from my question (because I oversimplified it). I need to search in a time range specified in hours AND MINUTES. For example, all records with events between 11:10 and 13:40 over several days. DATEPART() only covers one datetime field. How do I handle a time that includes hours and minutes? Many thanks Show quote "SQL" <denis.g***@gmail.com> wrote in message news:1137095323.912990.7040@g43g2000cwa.googlegroups.com... > If you are searching just one day then use Barry's method since it will > be faster > If you need to search for a period that is longer than 1 day then use > my solution > > > http://sqlservercode.blogspot.com/ > Did you see my answer?
Show quote "Andrew Chalk" <ach***@magnacartasoftware.com> wrote in message news:fxzxf.647$PL5.426@newssvr11.news.prodigy.com... >I am actually searching over several days. One other problem that I omitted >from my question (because I oversimplified it). I need to search in a time >range specified in hours AND MINUTES. For example, all records with events >between 11:10 and 13:40 over several days. > > DATEPART() only covers one datetime field. How do I handle a time that > includes hours and minutes? > > Many thanks > > "SQL" <denis.g***@gmail.com> wrote in message > news:1137095323.912990.7040@g43g2000cwa.googlegroups.com... >> If you are searching just one day then use Barry's method since it will >> be faster >> If you need to search for a period that is longer than 1 day then use >> my solution >> >> >> http://sqlservercode.blogspot.com/ >> > > Yes. I just tried it and it appears to work.
Many thanks, Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uUAKku7FGHA.2212@TK2MSFTNGP15.phx.gbl... > Did you see my answer? > > > "Andrew Chalk" <ach***@magnacartasoftware.com> wrote in message > news:fxzxf.647$PL5.426@newssvr11.news.prodigy.com... >>I am actually searching over several days. One other problem that I >>omitted from my question (because I oversimplified it). I need to search >>in a time range specified in hours AND MINUTES. For example, all records >>with events between 11:10 and 13:40 over several days. >> >> DATEPART() only covers one datetime field. How do I handle a time that >> includes hours and minutes? >> >> Many thanks >> >> "SQL" <denis.g***@gmail.com> wrote in message >> news:1137095323.912990.7040@g43g2000cwa.googlegroups.com... >>> If you are searching just one day then use Barry's method since it will >>> be faster >>> If you need to search for a period that is longer than 1 day then use >>> my solution >>> >>> >>> http://sqlservercode.blogspot.com/ >>> >> >> > > for a particular day, or for multiple days?
[these examples have 1:00 pm exactly included change to < to exclude] -- single day declare @today datetime set @today = dateadd(day,datediff(day,0,getdate()),0) select <columns> from events where event_date>=dateadd(hr,11,@today) and event_date<=dateadd(hr,13,@today) -- multiple days -- these would be parms declare @start datetime, @end datetime select @start = '20060109', @end = '20060113' select <columns> from events where event_date>=@start and event_date<@end+1 and event_date>=dateadd(hr,11,dateadd(day, datediff(day, 0, event_date), 0)) and event_date<=dateadd(hr,13,dateadd(day, datediff(day, 0, event_date), 0)) Andrew Chalk wrote: Show quote > I have the time of an event stored on each record as a datetime field.It > includes the year,month,day, etc. Suppose my user wants to search the table > for all events over the lunch hour, say between11am and 1pm. How do I > construct the SELECT query to peek into each datetime field and return only > those records that satify the specified time range? > > Many thanks. > >  |
|||||||||||||||||||||||
Other interesting topics