Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DateTime parameter conversion problem

Posted on 2003-11-17
16
Medium Priority
?
968 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
15 Comments
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9767731
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
ID: 9767786
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
ID: 9768138
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:ScottGutman
ID: 9768223
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
ID: 9768321
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
ID: 9768571
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
ID: 9770810
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
 

Author Comment

by:ScottGutman
ID: 9770822
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
ID: 9771206
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 400 total points
ID: 9771374
>>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
ID: 9771430
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
ID: 9771457
I just re-tested and that also worked for me.

Anthony
0
 

Author Comment

by:ScottGutman
ID: 9814014
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
ID: 9815217
>>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
ID: 9821889
Thanks for the points.

Anthony
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

824 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