Date format for ADP stored procedure

[Mouse]

Hi,

Need help from anybody ...

I used this statement in ADP stored procedure:
....... (dbo.billing.Date >= @DateFrom AND dbo.billing.Date
<= @DateTo)

But it does not produce the correct result ?
Did I miss anything ?

What is the correct declaration for date format ?
I used: @DateFrom datetime, is this correct ?
Should it be: nvarchar(50) or datetime ?


Thanks.

 

[Karen Vaudreuil]

What is the datatype of dbo.billing.Date? If it's datetime, then your
statement seems correct. You could also use dbo.billing.Date BETWEEN
@DateFrom AND @DateTo. The problem could be when you send these parameters
to the stored procedure. Does Access convert the dates correctly? There
could be a conflict between the Windows configuration and SQL Server dates.
The system sometimes switches Month and Day. To find out, check the value
of DateFrom and DateTo BEFORE the stored proc is executed (in Access) and
after it has started (for that insert a "SELECT DateFrom = @DateFrom, DateTo
= @DateTo" instead of your actual query. I always try to send the dates in
the international format 'YYYYMMDD'. (This works with Datetime fields.)
This way, it's always interpreted correctly.

You can also test manually the query by replacing the variables with real
dates like: dbo.billing.Date BETWEEN '20031101' AND '20031130'. If that
doesn't give you the right result then there's an error in your query. If
it does give the right results, the error is probably in date formating.

Karen