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 |
|
SimonGough
Starting Member
23 Posts |
 Posted - 2002-01-17 : 11:54:44
|
| Hi I have two date fields and an ID being passed to a SP. The code executes fine in SQL Query Analyser but when I call it I get an error.The two dates are passed as Date types using the following call:"up_parmsel_duplicate_vehicle('" & lngID & "'," & dtCommence & "," & dtComplete & ")"the SQL code for the SP is as follows:create procedure up_parmsel_find_duplicate(@ID int, @startdate datetime, @enddate datetime) as set dateformat dmyselect table1.job_number, commence_date, completion_date, table2.job_refID, product_IDfrom table1 join table2 on table1.id = table2.refIDwhere product_ID = @ID and (cast(commence_date as datetime) >= @startdate or cast(completion_date as datetime) <= @enddate)Can anyone see a problem with this as I have tried messing around loads of ways to no avail....Thanks for any insight.Simon. |
|
|
sica
Posting Yak Master
143 Posts |
Posted - 2002-01-17 : 17:42:12
|
| What error do you get?Maybe it has do with the way you call the sp.Sica |
 |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-01-17 : 18:08:57
|
| Hi SimonI'm a bit unsure, but are you calling the SP from VBScript? If you are then I think you'll need to '' (ie quote) your date value parameters. I'm unsure because it looks like you've quoted the ID field - ? The other thing I'm confused about is that the name of the SP appears to be different from the name in the SP definition - but I assume that's just a typo.Without knowing what the error is - I'll guess and say it could be to do with dateformat. I generally use CONVERT() rather than CAST() for date, so I can control which format I'm expecting. eg CONVERT(datetime, commence_date, 103) - for dd/mm/yyyy - see Books OnlineMaybe you could clear up a few of my questions and I can help you further? Sorry I'm not able to give you a direct answer yet.I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
SimonGough
Starting Member
23 Posts |
Posted - 2002-01-18 : 04:19:07
|
| Hi, yes the differing names is just a typo.I have tried with 'quotes', without. It always produces the same error.The error I am getting says it encoutered an error while casting a varchar to datetime. Presumably converting my vars - datetime type to varchar(as the fields are stored are stored in the table). I just don't get it??!? |
|
|
|
SimonGough
Starting Member
23 Posts |
Posted - 2002-01-18 : 04:35:25
|
| Ok, the exact error is this:[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetimehope that helps a bit... |
|
|
|
SimonGough
Starting Member
23 Posts |
Posted - 2002-01-18 : 05:01:16
|
| Ok, I just found the solution on this very site. I will paste it below in case anyone else is having the same problem as me. Thanks for your help....Simon----------------------------------------------------------SOLUTION:SQL Server is pretty picky about dates (and so am I :). Bascially if you get the format just a little bit wrong you will get this type of error. You can find a list of valid date formats in Books Online under the CONVERT statement.My suggestion would be to pass the date into the parameter a character string and explicitly convert it do a date field. Make @LIVE a VARCHAR(40) field. Then add the following lines:DECLARE @livedate DATETIMESELECT @livedate=CONVERT(datetime, @live)IF @ERROR <> 0 THEN /* Do some error processing */ELSE /* Do the insert using @livedate*/ |
|
|
|
|
|
|
|
|
Date Comparison - Cast error??