Re: Time range matching
From: Rami (Rami_at_discussions.microsoft.com)
Date: 08/09/04
- Next message: flologic: "job history"
- Previous message: Aaron [SQL Server MVP]: "Re: How to change the owner of a User Defined Data Type?"
- In reply to: Wayne Snyder: "Re: Time range matching"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 9 Aug 2004 07:09:01 -0700
Thank you all,
Uri Dimant, David Portas, Wayne Snyder
"Wayne Snyder" wrote:
> 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
> > > > > > > > > --
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>
- Next message: flologic: "job history"
- Previous message: Aaron [SQL Server MVP]: "Re: How to change the owner of a User Defined Data Type?"
- In reply to: Wayne Snyder: "Re: Time range matching"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
