Solved

DateTime parameter conversion problem

Posted on 2003-11-17
16
964 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
  • 2
  • +1
16 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 100 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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