• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

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
       
        Else
            NewTime = NewTime + CharCheck
        End If

    Loop
   
    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.
0
ScottGutman
Asked:
ScottGutman
  • 7
  • 5
  • 2
  • +1
1 Solution
 
namasi_navaretnamCommented:
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.
0
 
Anthony PerkinsCommented:
Post your stored procedure GetLeads.  If the @AgeDate is a date parameter than you should be passing a date not a varchar.

Anthony
0
 
ScottGutmanAuthor Commented:
CREATE PROCEDURE dbo.GetLeads
(@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)
ORDER BY ID DESC

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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

besides the variable agedate is created by

AgeDate = Now()
0
 
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
   .Open
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

rs.Close

cn.Close
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.

Anthony
0
 
namasi_navaretnamCommented:
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.

CREATE PROCEDURE dbo.GetLeads
(@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())
ORDER BY ID DESC
0
 
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.

0
 
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.
0
 
nmcdermaidCommented:
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.
0
 
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())

To:
.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.

Anthony

0
 
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
0
 
Anthony PerkinsCommented:
I just re-tested and that also worked for me.

Anthony
0
 
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))
0
 
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?
http://www.experts-exchange.com/help/closing.jsp#4

Thanks,
Anthoy
0
 
Anthony PerkinsCommented:
Thanks for the points.

Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 7
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now