Hello
I want to compare an inserted date against two stored dates in order to validate an entry. The date entered is being stored in a separate table from the stored dates.
I am not sure how to go about this. So far I have this:

create PROCEDURE sp_GetTime
@FullName varchar(50),
@ConsultantId numeric,
@App_Date datetime output
AS
SELECT *
FROM vwPatientConsultantAvailability vw inner Join Appointments app
on vw.ConsultantId = app.ConsultantId
where @FullName=FullName and @ConsultantId=ConsultantId and @App_Date=App_Date
if @App_Date < vw.AvailableStart

I realise I can add if statements to the procedure so if @App_Date between DateA and DateB then validate. if there is a better method please let me know.
Thanks.

Re: Stored Procedure to compare dates 80 80

thr like this

create PROCEDURE sp_GetTime
@FullName varchar(50),
@ConsultantId numeric,
@App_Date datetime output
AS
Declare @Cnt int
set @Cnt =0
SELECT  @Cnt= Count(*)
FROM vwPatientConsultantAvailability vw inner Join Appointments app
on vw.ConsultantId = app.ConsultantId
where @FullName=FullName and @ConsultantId=ConsultantId and @App_Date Between DateA And DateB

if(@Cnt >0 )
Begin
-- The Data Exists Between Two Date
End
Else
Begin
-- Data Does not exist Between Two Dates
End

All The Best

Re: Stored Procedure to compare dates 80 80

Hey Bhaarat, Thanks for the code but I keep getting errors. So I tried this as well:

create procedure dateCheck
 @App_Date datetime
as
select * from Derma_Constultants dc join Appointments app
on dc.ConsultantId=app.ConsultantId
if exists (select * from Derma_Consultants where AvailableStart<=@App_Date and AvailableEnd >= @App_Date)
begin
insert into Appointments(PatientId,P_FName,P_LName,ConsultantName,ConsultantId,@App_Date,AppointmentType
end
go

can you see anything i need to add. this is the error message:

Msg 102, Level 15, State 1, Procedure dateCheck, Line 8
Incorrect syntax near '@App_Date'.

Re: Stored Procedure to compare dates 80 80

Hey Managed to make it work, I had too many select statements in there. Thanks for your help. :)

Re: Stored Procedure to compare dates 80 80

Hey Thank you for the link. very helpful..

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.