May 29, 2008 at 10:40 am
We've got a simple table, whose (partial) definition is as follows:
CREATE TABLE [dbo].[Appointments](
[AppointmentID] [int] IDENTITY(1,1) NOT NULL,
[LastName] [varchar](28) NULL,
[FirstNames] [varchar](28) NULL,
[AppointmentType] [tinyint] NULL,
[StartTime] [smalldatetime] NULL,
[Duration] [smallint] NULL,
[AssignedStaff] [smallint] NULL,
[DateFilled] [smalldatetime] NULL,
[SpanishOnly] [tinyint] NOT NULL CONSTRAINT [DF_Appointments_SpanishOnly] DEFAULT (0),
[ProcessStatus] [tinyint] NULL CONSTRAINT [DF_Appointments_ProcessStatus] DEFAULT (0),
[ReferralSource] [smallint] NULL,
[Gender] [char](1) NULL,
[StreetAddress] [varchar](50) NULL,
[City] [varchar](20) NULL,
[State] [char](2) NULL,
[ZipCode] [char](5) NULL,
[Comments] [varchar](128) NULL,
CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED
(
[AppointmentID] ASC
)WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
I am trying to run a simple query to filter against StartTime, and I've tried using BETWEEN or less than and greater than symbols together, and I am not getting correct results at all. For example, the query, as it sits right now, looks like this:
select * from
Appointments
where StartTime >= '2008-5-29' and StartTime < '2008-7-1'
and (LastName is not null) or (FirstNames is not null)
order by StartTime desc
Instead of giving me data only between 5/29/2008 and 7/1/2008, it goes back several years?! Why is that? Is it because StartTime is defined to be a SmallDateTime data type?
Kindest Regards, Rod Connect with me on LinkedIn.
May 29, 2008 at 10:58 am
your OR is throwing you off.
I'm thinking you want your WHERE clause to be:
where StartTime >= '2008-5-29' and StartTime < '2008-7-1'
and ((LastName is not null) or (FirstNames is not null)) --<--notice the extra paren's
order by StartTime desc
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 10:59 am
select * from
Appointments
where StartTime >= '2008-5-29' and StartTime < '2008-7-1'
and (LastName is not null) or (FirstNames is not null)
order by StartTime desc
It's because your essentially saying ...
StartTime >= '2008-5-29'
AND StartTime < '2008-7-1'
AND (LastName is not null)
OR (FirstNames is not null)
So the last OR is getting you ...
So wrap the where clause in parens
select * from
Appointments
where StartTime >= '2008-5-29' and StartTime < '2008-7-1'
and ((LastName is not null) or (FirstNames is not null))
order by StartTime desc
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 29, 2008 at 11:00 am
The problem could be in the where clause , the last OR statement means that the other conditions are ignored and it will select where firstnames is null put '( )' around the where clauses
where startTime >= '2008-5-29' and StartTime < '2008-7-1'
and ((LastName is not null) or (FirstNames is not null))
order by StartTime desc
May 29, 2008 at 1:52 pm
Rod at work (5/29/2008)
OK, that was it. WOW, I feel so stupid; the answer is so obvious.Oh well, thank you everyone for helping me out of my brain fart.
It's only obvious once you see it....Not always so easy to see it when you wrote it.:P
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply