Re: Time range matching

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 07:55:51 -0400

IF you are trying to get all emps born during certain hourly ranges without
regard to the date, your proc will work..

However it is better NOT to use conversion functions in where clauses...
If this is a query which is done frequently you might want to add a virtual
column to the emp table which contains the time only,
then you can easily query against the time independently of the date.

This can also be useful any time you are looking to select rows using
datetime with a time range that spans multiple days... 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rami" <Rami@discussions.microsoft.com> wrote in message
news:9A57CF01-9117-481B-9F06-541CB99812D7@microsoft.com...
> 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: ALTER TABLE table ADD column_name
    ... Uri can probably confirm this, but I've always understood that if you change ... a table with Enterprise Manager, what it actually does is to copy the data ... name as the old one, but with the changed properties, then copy all the data ... I´m trying to add a new column in SQL Server 2000, ...
    (microsoft.public.sqlserver.server)
  • Re: Get last newsequentialid with output inserted
    ... I forgot say minimum is SQL Server 2005. ... Incorrect syntax near the keyword 'INSERT'. ... Uri: I am big fan of IDENTITY, but we cannot use it there. ... However, the foreign key ...
    (microsoft.public.sqlserver.programming)
  • Re: How to improve performance of the reports display while updating the table?
    ... Uri was saying that your select has an error in it, ... but you are using an OLD style syntax which is VERY prone to the ... I support the Professional Association of SQL Server and it's community of SQL Server professionals. ... did you mean that my result will be wrong after I use the nolock syntax? ...
    (microsoft.public.sqlserver.server)
  • Re: Using "Table" variable
    ... >> Table variables are actually created in TEMPDB ... >Does not SQL Server create and manage it in the memory? ... Hi Uri, ...
    (microsoft.public.sqlserver.programming)
  • Re: Yes No
    ... I prefer (like Uri) to use a charwith Y or N so the ... Wayne Snyder, MCDBA, SQL Server MVP ... Computer Education Services Corporation, Charlotte, NC ... community of SQL Server professionals. ...
    (microsoft.public.sqlserver.server)