VBA to SQL SP Interaction 1

[Mute101]

I have a Stored Procedure sat in my Server which I call from and Access2000 frontend, see code below;

CREATE PROCEDURE  dbo.procMailDates
@StartDate datetime,
@EndDate datetime,
@Account int
AS
SELECT OrderNo,[Purchase Order Number],KPShipmentID,[Required Delivery Date],DespatchDate,ConsignmentNo,DeliveredSignee,DeliveredDate,DeliveredTime,MainOrderNo,[Order Status],NoOfBoxes,ShipTown INTO MailTemp FROM Orders WHERE AccountNo = @Account AND convert(char(10),[Required Delivery Date],103)  BETWEEN convert(char(10),@StartDate,103) AND convert(char(10),@EndDate,103)
EXEC procMailUpdate
GO

I am having problems with the dates, the whole thing works but it doesnt limit the records down to within the date range that I pass it.

I have set everything up to make sure all parts of the application run as UK based and I am now stuck.

What am I doing wrong?

Simon ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[Mute101]

*BUMP*

Really need help on this one.

Sorry about the SQL above it looks messy here ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[sunila7]

Hi,

u r converting the date data type to a character. i think that is the problem, so y dont u use datetime instead,something like this.

SELECT OrderNo,[Purchase Order Number],KPShipmentID,[Required Delivery Date],DespatchDate,ConsignmentNo,DeliveredSignee,DeliveredDate,DeliveredTime,MainOrderNo,[Order Status],NoOfBoxes,ShipTown INTO MailTemp FROM Orders WHERE AccountNo = @Account AND convert(datetime,[Required Delivery Date],103) BETWEEN convert(datetime,@StartDate,103) AND convert(datetime,@EndDate,103)

Sunil

 

[tlbroadbent]

I prefer to declare date variables as char or varchar. Then execute the SP with a date format that SQL will interpret properly. SQL will not be able to select ranges if you use the British date format.

CREATE PROCEDURE dbo.procMailDates
@StartDate varchar(11),
@EndDate varchar(11),
@Account int
AS
SELECT
OrderNo,
[Purchase Order Number],
KPShipmentID,
[Required Delivery Date],
DespatchDate,
ConsignmentNo,
DeliveredSignee,
DeliveredDate,
DeliveredTime,
MainOrderNo,
[Order Status],
NoOfBoxes,
ShipTown
INTO MailTemp
FROM Orders
WHERE AccountNo = @Account
AND [Required Delivery Date]
BETWEEN @StartDate AND @EndDate

EXEC procMailUpdate
GO

Execute the procedure with the following syntax.

Exec dbo.procMailDates
'2002-05-01', '2002-05-28', 12345

You can also use 'mm/dd/yy' or 'mmm dd yyyy' formats.

Check out the following page for more information about SQL dates. Be sure to check out additional links on this page.

http://tlbroadbent.home.attbi.com/sql_and_access/sql_server_datetime.htm

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mute101]

Thankyou all for your input.

Much appreciated.

I have a functioning SP now, when I throw dates from access it still throws some funnies but I can sort em out.

Thanks for the website you have put together Terry it has already provided me with many answers.

Cheers
Simon ----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------