Store proc
-
hey i have the following proc, but it's returning null value.
[dbo]
.[Get_Appointments]
@this_date
datetime
as
Select
dbo.Patient_Info.Docket_num, dbo.Patient_Info.Last_Name,
dbo
.Patient_Info.First_Name,dbo.Scheduled_Appointment.App_Date
From
Patient_Info inner join Scheduled_Appointment
on
Patient_Info.KPAIDS_NO = Scheduled_Appointment.KPAIDS_NO
Where
dbo.Scheduled_Appointment.App_Date = convert(varchar(10),@this_date, 101)
Order
by dbo.Scheduled_Appointment.App_Date
i tested it using the following date format>> 5/17/2007
thx for ur help
-
Can you post a sample of what it is returning?
-SQLBill
-
should return>> ex
id First Name LastName Appiontment Date .......etc
23 bill cower 05/17/2007 ...........etc
but it returns Null
like
id First Name LastName Appointment Date..............etc
nothing here....
-
Tell me... is AppointmentDate a VARCHAR column?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
1. Are you sure there is data in the "Scheduled_Appointment" table for the date tested? (if not then where clause/join fails)
2. If #1 is true are you sure there is data (patients) in the "Patient_Info" table that have appointments for the date being tested? (if not then where clause/join fails)
3. If #1 and #2 are true then are you sure that scheduled_appointment.app_date is a varchar column? If it is a datetime column does it have "time" data included in it? If so the following proves you will never get a match:
declare @t_ table (date_ datetime)
insert into @t_ values (getdate())
select * from @t_ where date_ = convert(varchar(10),getdate(),101)
select * from @t_ where convert(varchar(10),date_,101) = convert(varchar(10),getdate(),101)
--First select statement returns no records because table record has time included and your test does not.
--Second select will result in a match.
James.
-
1. AppointmentDate is a Datetime data type.
ooops srry guys my bad i had AppointmentDate as char
.i changed it to datetime, now it works
thx again
-
Does AppointmentDate contain time information, or is the time portion all "zeros"? If it has time values then your query will never return anything. See the "second" select statement in my example for a method of over comming the problem (there is more than one way to fix it, but what I did will work).
James.
-
my mistake JLK... it's working now thx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply