Hey guyz(I had this in the Begining section but i wasn't gettin much help) i got a problem with dates in my project. I would like to get the date from my client program en pass it to the stored procedure in my database.

First i had to concatenate the date(example below) so as to be able to compare the dates when passed to the Stored procedure en also am in Australia where we use 01/05/2003 as our date format i had to change (concatenate) to 2003-05-01.

Public gFromDate,gToDate As String

Dim fDate As Date
Dim tDate As Date

fDate = Me.dtpFrom.Value
'concatenate the date string to be used SQL 7
gFromDate = Mid(fDate, 7, 10) + "-" + Mid(fDate, 4, 2) + "-" + Mid(fDate, 1, 2)
tDate = Me.dtpTo.Value
'MsgBox (wDate)
gToDate = Mid(tDate, 7, 10) + "-" + Mid(tDate, 4, 2) + "-" + Mid(tDate, 1, 2)
MsgBox (" gToDate -> " & gToDate & " gFromDate -> " & gFromDate)

Set objRst = Nothing

objRst.Open "Get_OrderRecords_By_Criteria(gFromDate,gToDat e)" _
, objConn, adOpenDynamic, adLockReadOnly, adCmdStoredProc

Set Me.HFPhones.DataSource = objRst

Error On the Green part::

Problem: if i declare gToDate and gFromDate As Date even after changing them it still remains the same format i.e 01/05/2003.

I also changed the variable types that am passing in the Stored procedure to Char from smalldatetime but still i get syntax error ( "converting character string to smalldate data type") when comparing the dates (an example of the stored procedure below)

CREATE PROCEDURE Get_OrderRecords_By_Criteria (
@fromDate char(10),
@toDate char(10)) AS

Select fldDate As RecDate,fldName As Customer
FROM tblOrderRecords
Where fldDate > @fromDate AND fldDate < @toDate
GO

My problem is how would i go about having the wright date passed to the Stored Proc from my Client Code.?? please help real time if any one can!!!


LION OF JUDDAH!

__________________
LION OF JUDDAH!

Hi,

I think I've faced this problem before. I think you should try doing the following,

fDate = CDate(Me.dtpFrom.Value)
tDate = CDate(Me.dtpTo.Value)

and that's it, you don't need to do anything else as long as the dates entered are of this format 'dd/mm/yyyy'. I'm assuming that 'Me.dtpFrom.Value' and 'Me.dtpTo.Value' are text boxes for entering the dates.

You need to revert back your Stored Procedure parameters to 'smalldatetime' for the above to work and for date comparison try using the T-Sql 'DateDiff' function. Also, using a command object would help as you would have more control on the parameter's data types that are passed back to the stored procedure.

Hope this helps.:)

Cheers dude.