SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 fetch status in a SP when using dates
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jon Hall
Starting Member

2 Posts

Posted - 04/20/2008 :  08:01:39  Show Profile  Reply with Quote
Firstly hello all.

I'm stuck, have a stored proceedure that is using fetch status to determine if an actual date, is between a start & end date. When i use variables for start & end date the SP does not work it returns a fetch status of -1, but if i hard code the same dates I'm expecting then I get a fetch status of 1.

C2
declare c2 cursor for
SELECT
CYP_ICS.dbo.ppmark.MarkerID, CYP_ICS.dbo.ppmeeting.ActualDate
FROM
CYP_ICS.dbo.ppmark
INNER JOIN CYP_ICS.dbo.ppmeeting ON CYP_ICS.dbo.ppmark.UnitRef = LTRIM(STR(CYP_ICS.dbo.ppmeeting.MeetingID) + '/com.liquidlogic.path.DBPathMeeting')
WHERE
(CYP_ICS.dbo.ppmeeting.MeetingType = 'CLA Review Meeting')
AND (CYP_ICS.dbo.ppmark.MainRef = LTRIM(Str(@p_id) + '/com.ics.DBPerson'))
AND (CYP_ICS.dbo.ppmeeting.ActualDate >= CONVERT(DATETIME, @c1_sdate, 102))
AND (CYP_ICS.dbo.ppmeeting.ActualDate <= CONVERT(DATETIME, @c1_edate, 102))
ORDER BY CYP_ICS.dbo.ppmeeting.ActualDate

can anyone please help...

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 04/20/2008 :  12:24:13  Show Profile  Visit nr's Homepage  Reply with Quote
Do the variables have the correct date format?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/20/2008 :  14:19:23  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
Use proper types for your variables and parameters, don't use VARCHAR for everything. If a variable or parameter should be a date, use DATETIME.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

Jon Hall
Starting Member

2 Posts

Posted - 04/21/2008 :  04:24:21  Show Profile  Reply with Quote
When set to datetime did not work returned -1 on fetch status, so I tried changing them to VARCHAR & converting them to be 100% that they had the same date time format. But if i hard code the dates as oposed to passing them it works a treat also if I use the same code but as a sub query & do not pass the dates it works, all be it slowly.
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/21/2008 :  08:52:50  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
>> When set to datetime did not work

Show us your code. If you are using correct data types everywhere and your code is correctly written it will not only work, but it will work more efficiently and you will also never have to worry about date formats.

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000