Re: Time range matching

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Rami (Rami_at_discussions.microsoft.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 01:51:02 -0700

Dear Uri,

I just read the artical:
http://www.sql-server-performance.com/bs_date_time.asp

It is a good artical but it did not solve the problem of the range between
two days. fro example:
                   from (23:00) To (1:00)

You did not tell me you opinion about the following stored procedure:

Declare @A varchar(50)
Declare @B varchar(50)

Set @A ='10:03:50:000'Set @B ='10:59:00:000'

if (@A<@B)
 Select * from emp where Convert ( varchar,empbirthdate,114) between @A and @B
Else
Select * from emp where Convert ( varchar,empbirthdate,114) not between @B
and @A

 What do you think about this approach?

"Uri Dimant" wrote:

> Rami
> Please visit this site.
> http://www.sql-server-performance.com/bs_date_time.asp
> "Rami" <Rami@discussions.microsoft.com> wrote in message
> news:4EB66AE4-05C6-4123-B8B1-60CFA67A5933@microsoft.com...
> > Dear Uri,
> >
> > I tried to use a stored procedure as the following:
> >
> > Declare @A varchar(50)
> > Declare @B varchar(50)
> >
> > Set @A ='10:03:50:000'Set @B ='10:59:00:000'
> >
> > if (@A<@B)
> > Select * from emp where Convert ( varchar,empbirthdate,114) between @A and
> @B
> > Else
> > Select * from emp where Convert ( varchar,empbirthdate,114) not between @B
> > and @A
> >
> >
> > What do you think about this approach?
> >
> > "Uri Dimant" wrote:
> >
> > > Rami
> > > I don't think you can retrieve the data between '23:00' and '01:00'
> > > regardless of date
> > >
> > >
> > >
> > > "Rami" <Rami@discussions.microsoft.com> wrote in message
> > > news:ACBFE077-21A8-4964-B0C1-0A4C944AD66D@microsoft.com...
> > > > Dear Uri,
> > > >
> > > > I think that you did not understand what I want. To from it in a
> question
> > > > let us say:
> > > >
> > > > what is the query that will retrieve all the employees born between
> 23:00
> > > > and 1:00, between the dates 1900-1-1 and 9999-1-1
> > > >
> > > > and
> > > >
> > > > what is the query that will retrieve all the employees born between
> 10:00
> > > > and 12:00, between the dates 1900-1-1 and 9999-1-1
> > > >
> > > > all what I'm looking for is to query information according to (time
> range)
> > > > despite the date.
> > > >
> > > >
> > > > "Uri Dimant" wrote:
> > > >
> > > > > Rami
> > > > > CREATE TABLE #Test
> > > > > (
> > > > > dt DATETIME NOT NULL
> > > > > )
> > > > > GO
> > > > > INSERT INTO #Test VALUES ('2004-08-09 09:22:37.170')
> > > > > INSERT INTO #Test VALUES ('2004-08-09 23:22:37.170')
> > > > > INSERT INTO #Test VALUES ('2004-08-10 01:22:37.170')
> > > > > INSERT INTO #Test VALUES ('2004-08-10 01:00:37.170')
> > > > > INSERT INTO #Test VALUES ('2004-08-10 02:00:37.170')
> > > > > GO
> > > > > DECLARE @dt DATETIME
> > > > > SELECT @dt='2004-08-09 23:00:00'
> > > > > SELECT * FROM #Test
> > > > > WHERE dt >=@dt AND dt <DATEADD (hour,3,@dt)
> > > > >
> > > > >
> > > > >
> > > > > "Rami" <Rami@discussions.microsoft.com> wrote in message
> > > > > news:45F33041-019E-48B3-8523-2F9BA23D1564@microsoft.com...
> > > > > > Thank you David, Uri,
> > > > > >
> > > > > > It worked, But in this way i can cover the events happendes only
> in
> > > one
> > > > > day,
> > > > > > for example:
> > > > > > From 10:00 to 12:00
> > > > > > but if i want to cover the events between two days, for example:
> > > > > > From 23:00 to 1:00
> > > > > > How can i do such a query?
> > > > > >
> > > > > > Rami,
> > > > > >
> > > > > > "David Portas" wrote:
> > > > > >
> > > > > > > You can try this:
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM Emp
> > > > > > > WHERE CONVERT(CHAR(8),birthdate,8)
> > > > > > > BETWEEN '10:00:00' AND '12:00:00'
> > > > > > >
> > > > > > > If you only need a date range based on whole hours then this
> might
> > > do
> > > > > it:
> > > > > > >
> > > > > > > SELECT *
> > > > > > > FROM Emp
> > > > > > > WHERE DATEPART(HOUR,birthdate) BETWEEN 10 AND 12
> > > > > > >
> > > > > > > but that's a different query from the one you asked for.
> > > > > > >
> > > > > > > --
> > > > > > > David Portas
> > > > > > > SQL Server MVP
> > > > > > > --
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

Relevant Pages

  • Re: Time range matching
    ... I tried to use a stored procedure as the following: ... Declare @B varchar ... "Uri Dimant" wrote: ... > regardless of date ...
    (microsoft.public.sqlserver.server)
  • Re: Issue in Convert?
    ... Uri Dimant wrote: ... > DECLARE @date1 DATETIME ... Paras ...
    (microsoft.public.sqlserver.programming)
  • Re: Issue in Convert?
    ... DECLARE @date1 DATETIME ... > Uri Dimant wrote: ...
    (microsoft.public.sqlserver.programming)
  • Re: Temporary table and procedure - resources
    ... niejaki: Uri Dimant z adresu napisał: ... > Have a look at sp_getapplock stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: Script runs in QA but fails when run as job.
    ... Uri Dimant wrote: ... >>DECLARE @names SYSNAME ... It seems that #DATABASES is also used in de stored procedure ...
    (microsoft.public.sqlserver.programming)