SQL between Problem with dates

[Jane]

Hi,
I have the following problem:

I have a table of users and a table of appointments that references
these users and has start and end times.

I want to select those users that have appointments at, or bridging,
the given time.

As a starter I've tried to do the following:

DECLARE @Date DATETIME
SET @Date = '19 March 2004 14:00:00' -- the appointment time we want
to find an
-- an available user for

SELECT UserId
FROM Appointments
WHERE CONVERT(char(30 ), ApptTime, 100) = CONVERT(char(30 ), @Date, 100)
OR CONVERT(char(30 ), @Date, 100)
BETWEEN CONVERT(char(30 ), ApptTime, 100)
AND CONVERT(char(30 ), ApptEndTime, 100)
)

Which I thought should give me all the users that have an appointment
at this time, or appointments that bridge the given time.

However, the BETWEEN part of this select doesn't seem to work, as I
have one user who has an appointment from 9 in the morning to 3 in the
afternoon and their ID is not returned in this select.

Any help gratefully appreciated.
Thanks

Jane

[David Portas]

Re: SQL between Problem with dates

I assume ApptTime and ApptEndTime are DATETIME columns, in which case you
are invalidating the DATETIME comparison by converting them to CHARs.

If @date is 2004-03-19 14:00:00 and the meeting *ends* at that time do you
want to include that Appointment in your result? If so, then you can use
BETWEEN:

DECLARE @date DATETIME
SET @date = '2004-03-19T14:00:00'

SELECT userid
FROM Appointments
WHERE @date BETWEEN appttime AND apptendtime

But maybe what you intended was this, which gives a different result:

SELECT userid
FROM Appointments
WHERE @date >= appttime AND @date < apptendtime

--
David Portas
SQL Server MVP
--

[Jane]

Re: SQL between Problem with dates

Thanks for that. What I'm actually trying to do is select all those
users that don't have an appointment on or bridging a given time, and
then select the other appointments they do have for that day - does
this sound possible? I think the between statement is more what I
want than the '>=' or '<' option.

I'm struggling somewhat! I always get incorrect results back.

Jane

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<Z5adnbxHs t2Nztvd4p2dnA@g iganews.com>...[color=blue]
> I assume ApptTime and ApptEndTime are DATETIME columns, in which case you
> are invalidating the DATETIME comparison by converting them to CHARs.
>
> If @date is 2004-03-19 14:00:00 and the meeting *ends* at that time do you
> want to include that Appointment in your result? If so, then you can use
> BETWEEN:
>
> DECLARE @date DATETIME
> SET @date = '2004-03-19T14:00:00'
>
> SELECT userid
> FROM Appointments
> WHERE @date BETWEEN appttime AND apptendtime
>
> But maybe what you intended was this, which gives a different result:
>
> SELECT userid
> FROM Appointments
> WHERE @date >= appttime AND @date < apptendtime[/color]

[David Portas]

Re: SQL between Problem with dates

CREATE TABLE Appointments (userid INTEGER, appttime DATETIME, apptendtime
DATETIME, CHECK (appttime<appte ndtime), PRIMARY KEY (userid, appttime))
[color=blue]
> users that don't have an appointment on or bridging a given time, and
> then select the other appointments they do have for that day[/color]

SELECT userid, appttime, apptendtime
FROM Appointments AS A
WHERE NOT EXISTS
(SELECT *
FROM Appointments
WHERE userid = A.userid
AND @date BETWEEN appttime AND apptendtime)
AND appttime < DATEADD(DAY,1,C ONVERT(CHAR(8), @date,112))
AND apptendtime > CONVERT(CHAR(8) ,@date,112)

--
David Portas
SQL Server MVP
--