Solved

DateTime parameter conversion problem

Posted on 2003-11-17
16
958 Views
Last Modified: 2012-06-27
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
Comment
Question by:ScottGutman
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Post your stored procedure GetLeads.  If the @AgeDate is a date parameter than you should be passing a date not a varchar.

Anthony
0
 

Author Comment

by:ScottGutman
Comment Utility
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
 

Author Comment

by:ScottGutman
Comment Utility
i tried the Cdate conversion.  it did not work.

besides the variable agedate is created by

AgeDate = Now()
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 15

Expert Comment

by:namasi_navaretnam
Comment Utility
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
 

Author Comment

by:ScottGutman
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:ScottGutman
Comment Utility
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
 
LVL 30

Expert Comment

by:nmcdermaid
Comment Utility
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 100 total points
Comment Utility
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
I just re-tested and that also worked for me.

Anthony
0
 

Author Comment

by:ScottGutman
Comment Utility
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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
Thanks for the points.

Anthony
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now