Passing Dates to SQL From Excel

[DBAL]

Hi guys, I need to have the user enter a starting date
and an ending date to pass to the SQL server for
retrieving invoices. I'm just having trouble with the
datatypes and conversions.

Should I obtain the users input as a string?? Or should
I set the users input to a date datatype. I want the
user to be able to enter the date in this format
mm/dd/yyyy.

I tried to convert the invoice date to a string for
comparison with my variables but it didn't work.

The table.column_name is Armast.finvdate, and my
variables are strStartDate & strEndDate.

This SQL statement will be embedded in the VBA behind
Excel... but I have to get it right:

WHERE CONVERT(CHAR(10),Armast.finvdate,101) BETWEEN
strStartDate AND strEndDate

What is the best way to do this? What datatypes should I
declare the StartDate & EndDate? And should I convert
Armast.finvdate to match the variables or convert the
variables to try and match SQL standard date format?

DBAL

 

[Jamie Collins]

I need to have the user enter a starting date
and an ending date to pass to the SQL server for
retrieving invoices.

I tried to convert the invoice date to a string for
comparison with my variables but it didn't work.

My preferred approach is to store in memory as a VBA Date type and use
an unambiguous date string in sql code e.g.

WHERE MyDateCol BETWEEN
'01 JUN 2004' AND '30 JUN 2004'

This SQL statement will be embedded in the VBA behind
Excel...

My preferred approach is to use a stored procedure e.g.

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

and call it from Excel using e.g.

EXEC MyStoredProc '01 JUN 2004', '30 JUN 2004'

Jamie.

--

 

[DBAL]

Thanks Jamie, I will try and work with this on Monday..
THanks alot. DBAL>