Date Format problem in a Function query in ADP

[danielhis]

Hi I'm in the process of upgrading my mdb to an adp (using sql desktop edition for the moment) and although I used the upsize wizard, it seems like I'm having to rewrite almost everything. This is one of the more frustrating problems.

I have created a funtion and need to query a date column - Between @begindate and @enddate on on the input parameters on the form, I've set these two parameters to two text boxes on a form. The problem I'm having is the date format. As I understand it, sql server is base on American date format mm/dd/yyyy but as I'm in Australia my my system date is dd/mm/yyyy. I have tried so many different combinations of the following query and cannot qet it to work.

The following example works correctly, but only up until the 12th day

SELECT EmployeeID, LeaveDate, CASE (LeaveType) WHEN 1 THEN 1 WHEN 2 THEN 0.5 ELSE 0 END AS ALeave, CASE (LeaveType)WHEN 3 THEN 1 WHEN 4 THEN 0.5 ELSE 0 END AS PLeave
FROM dbo.LeaveTaken
WHERE (EmployeeID = @EmpId) AND (LeaveDate BETWEEN CONVERT(char(10), @Begindate, 103) AND CONVERT(char(10), @Enddate, 103))

I've tried converting the Leavedate and Begin/EndDates to 101, 102, 103, various combinations, changing the date format of the text boxes on the form but I think the system reads the dates on the form as dd/mm/yyyy (system format)no matter what and sql reads the dates as American before converting them. I'm not sure why the above function works up until the 12th because if it were reading the dates as american, the query result would be incorrect. It still however, fails when you get to the 13th day so I assume somewhere in the process, it thinks that 13 is the month.

What was a simple date query in an mdb seems to be causing me no end of problems as I need to use this 'between 1st date and 2nd date' on many forms and reports.

 

[cmmrfrds]

How are @Begindate and @Enddate defined in the stored procedure? As datetime?

Have you tried sending it as datetime assuming LeaveDate is datetime.

WHERE (EmployeeID = @EmpId) AND (LeaveDate BETWEEN @Begindate AND @Enddate)

 

[danielhis]

Yes, they are defined as datetime and I have tried;

WHERE (EmployeeID = @EmpId) AND (LeaveDate BETWEEN @Begindate AND @Enddate)

but unfortunatley that gets the dates mixed up.

e.g. if the begin and end dates where 01/08/04 to 12/08/04 (01 Aug 04 to 12 Aug 04) the returned results would be from the range 08 Jan 04 to 08 Dec 04.

 

[LuckyLuciano]

try to send the date in format 'dd-mm-yyyy' works always for me.

 

[cmmrfrds]

Did you try converting to the US format before sending - as a work around. Would need to move to a string and then back to the US format.

Dim var As String, var2 As String
'assume dd/mm/yy format.
var = Format("01/08/04", "dd/mm/yy")
Debug.Print var
var2 = Mid(var, 4, 3) & Mid(var, 1, 3) & Mid(var, 7, 2)
Debug.Print CDate(var2) '- will be 8/1/04

 

[cjowsey]

I'm in Australia and have never had this problem with BETWEEN dates in Access 2002. It sounds to me like your date is not what you think it is. Are the fields that you are passing into the stored procedure declared or formatted as dates?

Have you tried a

debug.print "Day is " & datepart("d", begindate)
debug.print "Month is " & datepart("m", begindate)

to make sure Access thinks the same way as you do?