DateTime parameter conversion problem

This is a wierd problem.  I have solved it but i don't know why and i am afraid that it will crop up again.
I writing this program in Access 2002, and using MS SQL server 2002 on a ms server 2000.

This is a function in my Access *.ADP program

    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    Set cmd.ActiveConnection = CurrentProject.Connection

    cmd.CommandText = "GetLeads"
    cmd.CommandType = adCmdStoredProc
    'Set the parameters
    cmd.Parameters.Append cmd.CreateParameter("@LeadStart", adVarChar, adParamInput, 20, LeadStart)
    cmd.Parameters.Append cmd.CreateParameter("@LeadClass", adVarChar, adParamInput, 20, LeadClass)
    cmd.Parameters.Append cmd.CreateParameter("@AgeDate", adVarChar, adParamInput, 20, AgeDate)
    Set GetLeadList = New ADODB.Recordset
    Set GetLeadList = cmd.Execute

I wrote this to pass variables to a stored procedure.  Works awesome, super fast.

This worked great for about 3 weeks.  Then i started getting an error about converting the variable(don't remember the exact words).  I tracked the error to the Agedate variable.  The answer that solved it was to shorten the date time string.  For exmaple, i removed a space befor am/pm and the '20' from 2003.  In other words This string might fail "11/17/2003 10:20:00 PM" but this one "11/17/03 10:20:00PM" would not.

I even wrote this bit of code to do the deed:
Function RemoveTimeSpace(TS)
    i = 0
    SpaceFlag = 0
    NewTime = ""
    TS = CStr(TS)
    Do Until i = Len(TS)
        i = i + 1
        CharCheck = Mid(TS, i, 1)
        If CharCheck = " " Then SpaceFlag = SpaceFlag + 1
        If CharCheck = "2" Then
            Test2003 = Mid(TS, i, 4)
            If Test2003 = 2003 Then
                i = i + 2
                CharCheck = Mid(TS, i, 1)
            End If
        End If
        If SpaceFlag = 2 And CharCheck = " " Then
            NewTime = NewTime + CharCheck
        End If

    RemoveTimeSpace = NewTime
End Function

I am afraid this error will return.  I don't know what to remove next or why this even worked.  Can some one explain this.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Try this with old dateformat 11/17/2003 10:20:00 PM

 cmd.Parameters.Append cmd.CreateParameter("@AgeDate", adVarChar, adParamInput, 20, CDate(AgeDate))

CDate Examples:
MyDate = "October 19, 1962"   ' Define date.
MyShortDate = CDate(MyDate)   ' Convert to Date data type.
MyTime = "4:35:47 PM"         ' Define time.
MyShortTime = CDate(MyTime)   ' Convert to Date data type.

Use IsDate to check to see if valid date

IsDate Examples:
Dim MyDate, YourDate, NoDate, MyCheck
MyDate = "October 19, 1962": YourDate = #10/19/62#: NoDate = "Hello"
MyCheck = IsDate(MyDate)   ' Returns True.
MyCheck = IsDate(YourDate)   ' Returns True.
MyCheck = IsDate(NoDate)   ' Returns False.
Anthony PerkinsCommented:
Post your stored procedure GetLeads.  If the @AgeDate is a date parameter than you should be passing a date not a varchar.

ScottGutmanAuthor Commented:
(@LeadStart int,
@LeadClass int,
@AgeDate datetime)
AS SELECT     TOP 1000 ID, State, Scheduled, Class, Aquired
FROM         dbo.Lead
WHERE     (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < @AgeDate)

dumb question, how do i pass a date and not Varchar.

I got the parameter code by cuttin and pasting.  I don't really understand it.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ScottGutmanAuthor Commented:
i tried the Cdate conversion.  it did not work.

besides the variable agedate is created by

AgeDate = Now()
Anthony PerkinsCommented:
This works for me (change the values of the parameters as appropriate):

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
   .ConnectionString = MY_CONN_STRING
End With

Set cmd = New ADODB.Command
With cmd
   Set .ActiveConnection = cn
   .CommandText = "GetLeads"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("@LeadStart", adInteger, adParamInput, 0, 12345)
   .Parameters.Append .CreateParameter("@LeadClass", adInteger, adParamInput, 0, 67890)
   .Parameters.Append .CreateParameter("@AgeDate", adDBTimeStamp, adParamInput, 0, Now())
   Set rs = .Execute
End With
Set cmd = Nothing

'Rest of your recordset code goes here


Set cn = Nothing

Notice that it is not necessary to instantiate the Recordset parameter prior to using it.  The Command object's Execute method does it.

If AgeDate is now() then you do not even have to pass date to this proc.

You can remove agedate parameter and then add (Aquired < getdate()) to proc.

(@LeadStart int,
@LeadClass int)
AS SELECT     TOP 1000 ID, State, Scheduled, Class, Aquired
FROM         dbo.Lead
WHERE     (Scheduled = 0) AND (ID < @LeadStart) AND (Class = @LeadClass) AND (Aquired < getdate())
ScottGutmanAuthor Commented:
To acperkins:

This is the error I got from using the   < .Parameters.Append .CreateParameter("@AgeDate", adDBTimeStamp, adParamInput, 0, Now())>

   Run-time error '-2147217913 (80040e07)':

   The conversion of a char data type to a datetime data type resulted in

   an out-of-range datetime value.

ScottGutmanAuthor Commented:
To namasi_navaretnam:

I really want to pass the variable for flexibility purposes.  I will need to pass dates on other SP's. Thanks for the input.
SQL has probably got day and month mixed up.

Does every day from the 13th onwards fail?

In Australia, 11/17/2003 will fail because there is no 17th month. Even though you think you're passing the date.
Anthony PerkinsCommented:
>>The conversion of a char data type to a datetime data type resulted in  an out-of-range datetime value.<<
I don't get it.  I tested it here and it worked fine.  Is it possible that you have two different date formats on your workstation as opposed to your server?  

Otherwise try changing this line:
.Parameters.Append .CreateParameter("@AgeDate", adDBTimeStamp, adParamInput, 0, Now())

.Parameters.Append .CreateParameter("@AgeDate", adDBTimeStamp, adParamInput, 0, Format$(Now(), "YYYY-MM-DD HH:NN:SS")

I have not tested this, but it should give you an unequivocal date.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Anthony PerkinsCommented:
I missed a final paranthesis, it should be:
.Parameters.Append .CreateParameter("@AgeDate", adDBTimeStamp, adParamInput, 0, Format$(Now(), "YYYY-MM-DD HH:NN:SS"))

Anthony PerkinsCommented:
I just re-tested and that also worked for me.

ScottGutmanAuthor Commented:
I learned to fix the problem in another question. The problem was fixed by increasing  the size of varchar to 255. may be not correct but works great.

Thanks for your help.

(@AgeDate Varchar(255))
Anthony PerkinsCommented:
>>may be not correct but works great.<<
Fair enough.  I do not agree with your solution, but if it works for you that is all that counts ...

Please close this question.  Here is how:

I answered my question myself. What do I do?

Anthony PerkinsCommented:
Thanks for the points.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.