Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jon Hall
Starting Member
2 Posts |
 Posted - 2008-04-20 : 08:01:39
|
| 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.C2declare c2 cursor for SELECT CYP_ICS.dbo.ppmark.MarkerID, CYP_ICS.dbo.ppmeeting.ActualDateFROM 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.ActualDatecan anyone please help... |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-20 : 12:24:13
|
| 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. |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-04-20 : 14:19:23
|
| 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Jon Hall
Starting Member
2 Posts |
Posted - 2008-04-21 : 04:24:21
|
| 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. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-04-21 : 08:52:50
|
| >> When set to datetime did not workShow 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.- Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
|
|
|
|
|
fetch status in a SP when using dates