How to Compare the user Given date with the table date using Store Procedure.

Question

question

NRK asked Dec 13, '10

How to Compare the user Given date with the table date using Store Procedure.

hi I have one date fields represents StartDate Using CalenderExtender and textbox field Represents ID. Im passing the 2 parameter from Code-Behind( ASP.NET) to Stored Procedure..... IN the Table having 4 columns **ID**,**StartDate**,**Enddate** and **ReturntoWorkDate**: My need is If i pass the parameter from Code-Behind it should compare with **StartDate**, **enddate** and **ReturntoWorkDate** and it should displays the record from the Table. Examlple In the table **ID** : 001 **startdate** : 12-11-2010 **EndDate** : 12-19-2010 **ReturntoWorkDate** : 12-20-2010 i'm passing this parameters **ID** : 001 and **Startdate** : 12-13-2010 **OUTPUT:** **ID** :001 || **Startdate** : 12-11-2010 || **Enddate** : 12-19-2010 || **ReturntoWorkDate** : 12-20-2010 For Above Question **NEED STORED PROCEDURE ONLY** This is the code..... Alter PROCEDURE [dbo].[sp_SearchLeaveDate] @employeeid varchar(30), @leavestartdate varchar(30) As Begin Declare @employee varchar(max) declare @leavestart varchar(Max) select @employee = employeeid, @leavestart = leavestartdate from LeaveRequest where employeeid = @employeeid and (between leavestartdate >=@leavestartdate or leaveenddate <= @leavestartdate) end Can any one Help me please.............

sql-server-2005 asp.net

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-12-13T04:00:21Z

Fatherjack answered Dec 13, '10

`between` is used in this context: `WHERE ColData BETWEEN ValueA AND ValueB` try altering your procedure to follow that format and if it still fails re-post your code.

6

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NRK commented · Dec 13, 2010 at 04:05 AM

FatherJack : I re arrange the code but it return only null value Alter PROCEDURE [dbo].[sp_SearchLeaveDate] @employeeid varchar(30), @leavestartdate varchar(30) As Begin Declare @employee varchar(max) declare @leavestart Datetime select count(*) from LeaveRequest where employeeid = @employeeid and @leavestart between leavestartdate and returndate End

0 ·

NRK commented · Dec 13, 2010 at 04:23 AM

FatherJack above reterives some values, but I need to check Whether the Given leavestart Date is presented in between the leavestartdate and returndate in the table LeaveRequest....

0 ·

Håkan Winther commented · Dec 13, 2010 at 05:11 AM

If you get a result when you use @leavestart between leavesartdate and returndate then you can be sure that the given leavestart is between. It is the same as saying (leavestartdate =@leavestart)

0 ·

NRK commented · Dec 13, 2010 at 05:59 AM

@Hakan : yeah right but it cannot return date In Between two dates I think so.

0 ·

NRK commented · Dec 13, 2010 at 06:00 AM

Thanks For your Comments.... Finally I found the Answer...

0 ·

Show more comments

Answer 2 · 2010-12-13T05:16:05Z

Håkan Winther answered Dec 13, '10

This answer has nothing to do with your question, but I want to give you an advice. You NOT should prefix your procedures with sp_ This is why: > Don't use the prefix "sp_" in the > stored procedure name if you need to > create a stored procedure to run in a > database other than the master > database. The prefix "sp_" is used in > the system stored procedures names. > Microsoft does not recommend to use > the prefix "sp_" in the user-created > stored procedure name, because SQL > Server always looks for a stored > procedure beginning with "sp_" in the > following order: the master database, > the stored procedure based on the > fully qualified name provided, the > stored procedure using dbo as the > owner, if one is not specified. So, > when you have the stored procedure > with the prefix "sp_" in the database > other than master, the master database > is always checked first, and if the > user-created stored procedure has the > same name as a system stored > procedure, the user-created stored > procedure will never be executed. Read more at: [ http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm][1] [1]: http://www.mssqlcity.com/Tips/stored_procedures_optimization.htm

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-12-13T06:26:22Z

NRK answered Dec 13, '10

i think this will work....... for my self it's working.... ALTER PROCEDURE [dbo].[sp_SearchLeaveDate] @employeeid varchar(30), @leavestartdate varchar(30) As Begin Declare @employee varchar(max) declare @leavestart Datetime set @leavestart = @leavestartdate select count(*) from LeaveRequest where employeeid = @employeeid and @leavestart between leavestartdate and returndate and status = 'Pending' or status = 'Approved' End

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther commented · Dec 13, 2010 at 06:38 AM

Make sure you are using parantheses on proper places so you don't get wrong data: where (employeeid = @employeeid and @leavestart between leavestartdate and returndate and status = 'Pending') or status = 'Approved' is NOT the same as : where employeeid = @employeeid and @leavestart between leavestartdate and returndate and (status = 'Pending' or status = 'Approved') If you don't use a paranthes it's unclear what you want and in this case this is what you get: where (employeeid = @employeeid and @leavestart between leavestartdate and returndate and status = 'Pending') or status = 'Approved'

2 ·

question