where datetime between 2PM and 3PM

microsoft.public.sqlserver.programming

15 Sep 2006 1:29 PM

newscorrespondent

I need to select from a datetime column based on time of day. I don't see
any functions to do this. Did I miss a section in BOL?

Is the best way to do this a function That takes the datetime, from and
through values and return a yes or no?

where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1

Thanks

15 Sep 2006 1:35 PM

SQL Menace

For today this would be your query
where Value >= DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0,
0))
and Value < DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))

run this to test
select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)) ,
DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))

if you want other days you can use a variable

declare @d datetime
select @d = '20060203'
select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0)) ,
DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0))

Denis the SQL Menace
http://sqlservercode.blogspot.com/

newscorrespond***@charter.net wrote:
Show quote

> I need to select from a datetime column based on time of day. I don't see
> any functions to do this. Did I miss a section in BOL?
>
> Is the best way to do this a function That takes the datetime, from and
> through values and return a yes or no?
>
>
> where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1
>
> Thanks

15 Sep 2006 1:45 PM

SQL Menace

Or if you need all dates you can do this

select * from YourTable
where datepart(hh,YourDateColumn)= 14

This will return all dates that had a time period between 14:00PM and
14:59:59

Denis the SQL Menace
http://sqlservercode.blogspot.com/

SQL Menace wrote:
Show quote

> For today this would be your query
> where Value >= DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0,
> 0))
> and Value < DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
>
>
>
> run this to test
> select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)) ,
> DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
>
>
> if you want other days you can use a variable
>
> declare @d datetime
> select @d = '20060203'
> select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0)) ,
> DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0))
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> newscorrespond***@charter.net wrote:
> > I need to select from a datetime column based on time of day. I don't see
> > any functions to do this. Did I miss a section in BOL?
> >
> > Is the best way to do this a function That takes the datetime, from and
> > through values and return a yes or no?
> >
> >
> > where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1
> >
> > Thanks

15 Sep 2006 1:50 PM

Jim Underwood

I think the OP wants all entries, on any day, that are between 2 and 3 PM.
We can check the hour and see if it is = 2, or check a range.

where datepart(hour,DATE_COLUMN) = 14

OR

where datepart(hour,DATE_COLUMN) >= 14
and datepart(hour,DATE_COLUMN) < 15

Show quote

"SQL Menace" <denis.g***@gmail.com> wrote in message
news:1158327314.222001.176340@d34g2000cwd.googlegroups.com...
> For today this would be your query
> where Value >= DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0,
> 0))
> and Value < DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
>
>
>
> run this to test
> select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)) ,
> DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
>
>
> if you want other days you can use a variable
>
> declare @d datetime
> select @d = '20060203'
> select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0)) ,
> DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0))
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
> newscorrespond***@charter.net wrote:
> > I need to select from a datetime column based on time of day. I don't
see
> > any functions to do this. Did I miss a section in BOL?
> >
> > Is the best way to do this a function That takes the datetime, from and
> > through values and return a yes or no?
> >
> >
> > where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1
> >
> > Thanks
>

15 Sep 2006 1:53 PM

SQL

Yep I realized that (after taking a couple of sips of Diet-Coke) and
have posted another reply

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Jim Underwood wrote:
Show quote

> I think the OP wants all entries, on any day, that are between 2 and 3 PM.
> We can check the hour and see if it is = 2, or check a range.
>
> where datepart(hour,DATE_COLUMN) = 14
>
> OR
>
> where datepart(hour,DATE_COLUMN) >= 14
> and datepart(hour,DATE_COLUMN) < 15
>
> "SQL Menace" <denis.g***@gmail.com> wrote in message
> news:1158327314.222001.176340@d34g2000cwd.googlegroups.com...
> > For today this would be your query
> > where Value >= DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0,
> > 0))
> > and Value < DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
> >
> >
> >
> > run this to test
> > select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)) ,
> > DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
> >
> >
> > if you want other days you can use a variable
> >
> > declare @d datetime
> > select @d = '20060203'
> > select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0)) ,
> > DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0))
> >
> > Denis the SQL Menace
> > http://sqlservercode.blogspot.com/
> >
> >
> > newscorrespond***@charter.net wrote:
> > > I need to select from a datetime column based on time of day. I don't
> see
> > > any functions to do this. Did I miss a section in BOL?
> > >
> > > Is the best way to do this a function That takes the datetime, from and
> > > through values and return a yes or no?
> > >
> > >
> > > where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1
> > >
> > > Thanks
> >

15 Sep 2006 1:59 PM

Jim Underwood

yup. I saw that. beat my post by about 5 minutes.

I really should learn to hit refresh before hitting send.

Show quote

"SQL" <denis.g***@gmail.com> wrote in message
news:1158328408.782343.77190@e3g2000cwe.googlegroups.com...
> Yep I realized that (after taking a couple of sips of Diet-Coke) and
> have posted another reply
>
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
>
>
> Jim Underwood wrote:
> > I think the OP wants all entries, on any day, that are between 2 and 3
PM.
> > We can check the hour and see if it is = 2, or check a range.
> >
> > where datepart(hour,DATE_COLUMN) = 14
> >
> > OR
> >
> > where datepart(hour,DATE_COLUMN) >= 14
> > and datepart(hour,DATE_COLUMN) < 15
> >
> > "SQL Menace" <denis.g***@gmail.com> wrote in message
> > news:1158327314.222001.176340@d34g2000cwd.googlegroups.com...
> > > For today this would be your query
> > > where Value >= DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0,
GETDATE())+0,
> > > 0))
> > > and Value < DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0,
0))
> > >
> > >
> > >
> > > run this to test
> > > select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0)) ,
> > > DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, GETDATE())+0, 0))
> > >
> > >
> > > if you want other days you can use a variable
> > >
> > > declare @d datetime
> > > select @d = '20060203'
> > > select DATEADD(hh,14,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0)) ,
> > > DATEADD(hh,15,DATEADD(dd, DATEDIFF(dd, 0, @d)+0, 0))
> > >
> > > Denis the SQL Menace
> > > http://sqlservercode.blogspot.com/
> > >
> > >
> > > newscorrespond***@charter.net wrote:
> > > > I need to select from a datetime column based on time of day. I
don't
> > see
> > > > any functions to do this. Did I miss a section in BOL?
> > > >
> > > > Is the best way to do this a function That takes the datetime, from
and
> > > > through values and return a yes or no?
> > > >
> > > >
> > > > where IsInTimeFrame(DateTimeColumn, FromTime, ThroughTime) = 1
> > > >
> > > > Thanks
> > >
>