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

x
?
Solved

Pervasive error when Using date add function and using result in query

Posted on 2008-06-25
12
Medium Priority
?
1,561 Views
Last Modified: 2012-05-05
I have written a program that writes data to text files.  For one of the textfiles I need to check the date 30 days in the past and write the data results from that date forward.  The code I'm using is below and the error I receive is attached.  Any help is greatly appreciated!!  Thanks!!!

If System.IO.File.Exists(FILE_NAME) = True Then

            Dim LastMonth As Date = DateAdd(DateInterval.Day, -30, Date.Today)
            Convert.ToString(LastMonth)


            mySelectQuery6 = " "
            mySelectQuery6 = mySelectQuery6 & "SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount "
            mySelectQuery6 = mySelectQuery6 & "FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor "
            mySelectQuery6 = mySelectQuery6 & "WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34) AND (CMCheck.CheckDate > " & LastMonth & ")) "
            mySelectQuery6 = mySelectQuery6 & "ORDER BY CMCheck.CheckNo; "


        Else

            mySelectQuery6 = " "
            mySelectQuery6 = mySelectQuery6 & "SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount "
            mySelectQuery6 = mySelectQuery6 & "FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor "
            mySelectQuery6 = mySelectQuery6 & "WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34)) "
            mySelectQuery6 = mySelectQuery6 & "ORDER BY CMCheck.CheckNo; "

        End If
        'Pass the file path and the file name to the StreamWriter constructor.
        myCommand = New OdbcCommand(mySelectQuery6, myConnection)
        Dim objStreamWriter6 As StreamWriter
        objStreamWriter6 = New StreamWriter("C:\Rolligon\Data\POPayHist.txt")

        myConnection.Open()


        header(0) = "VendorID"
        header(1) = "ERP"
        header(2) = "CheckNo"
        header(3) = "CheckDate"
        header(4) = "CheckAmt"
        header(5) = " "
        header(6) = " "
        header(7) = " "
        header(8) = " "
        header(9) = " "
        header(10) = " "
        header(11) = " "
        header(12) = " "
        header(13) = " "
        header(14) = " "
        header(15) = " "
        header(16) = " "
        header(17) = " "
        header(18) = " "
        header(19) = " "
        header(20) = " "
        header(21) = " "
        header(22) = " "
        header(23) = " "
        header(24) = " "
        header(25) = " "
        header(26) = " "
        header(27) = " "
        header(28) = " "
        header(29) = " "
        header(30) = " "
        header(31) = " "
        header(32) = " "
        header(33) = " "
        header(34) = " "
        header(35) = " "
        header(36) = " "

        objStreamWriter6.WriteLine(String.Join(ControlChars.Tab, header))
        Try
            myReader = myCommand.ExecuteReader()
        Catch ex As OdbcException
            MsgBox(ex.Message)
        End Try
Pervasive-error.doc
0
Comment
Question by:stephaniem712
  • 6
  • 4
  • 2
12 Comments
 
LVL 18

Expert Comment

by:mirtheil
ID: 21868344
WHat is that actual query being sent to Pervasive when it fails?  Also, what version of PSQL are you using?  It could be a formatting issue of the date being passed in or the value of the date.  
0
 

Author Comment

by:stephaniem712
ID: 21868433
The first select query where I use the LastMonth variable.
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21868619
It looks like the DATE variable LastMonth is not being formatted correctly, resulting  in the incompatible type error.  To troubleshoot this, please add a line to print out the entire SQL statement (mySelectQuery6) and see what it looks like.  If you are comparing the date against a date field, then you may need to force-format it as a date, i.e. " { d '2008-05-25'} ".

You'll see what's wrong when you print the SQL query...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:stephaniem712
ID: 21868829
Result:
" SELECT DISTINCT APVendor.ID, CMCheck.CheckNo, CMCheck.CheckDate, CMCheck.Amount FROM APVendor INNER JOIN CMCheck ON APVendor.SKAPVendor = CMCheck.FKAPVendor WHERE(((CMCheck.Status) <> 2 And (CMCheck.Status) <> 34) AND (CMCheck.CheckDate > 5/26/2008)) ORDER BY CMCheck.CheckNo; "

Is this what you're looking for?
0
 
LVL 18

Expert Comment

by:mirtheil
ID: 21868954
Yep, that's the problem.  The PSQL date format needs to be either 'yyyy-mm-dd' (with the single quotes) like '2008-05-26' or the forced format date like " {d '2008-05-25'} "
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 2000 total points
ID: 21869025
Exactly.  The text "5/26/2008" is being interpretted by the SQL parser as the numeric expression 5 / 26 / 2008, which is approximately 0.00009577.  It is then comparing a FLOAT with a DATE, resulting in your error.

One solution is to use the explicit DATE format, such as:  {d '2008-05-26'} instead.  You could also try to use the CONVERT function, but this will still require that you format the date string correctly.  Be sure to include the single quotes, too!
0
 

Author Comment

by:stephaniem712
ID: 21869314
How can I convert to the explicit date format?   I'll need to do it after this..

Dim LastMonth As Date = DateAdd(DateInterval.Day, -30, Date.Today)
          The below doesn't work....
            Convert.ToDateTime(LastMonth)
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21869392
Remember that with SQL statements, you are building strings to create the statement.

"(CMCheck.CheckDate > " & LastMonth & ")) "
will become something more like:

(CMCheck.CheckDate > {d '" & YEAR(LastMonth) & "-" & MONTH(LastMonth) & "-" & DAY(LastMonth) & "'} )) "

My VB is a bit rusty, so you may wish to make sure that the functions are correct.  As before, print the SQL statement to verify that it has proper syntax.
0
 

Author Comment

by:stephaniem712
ID: 21869706
The functions are correct but for some reason on the day function, it says that it's a "type'" and can't be used as an expression..
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 21869763
VB functions should work similarly to VBScript, right?  I found those here:
    http://www.w3schools.com/VBscript/vbscript_ref_functions.asp
Maybe there's another alternative?
0
 

Author Comment

by:stephaniem712
ID: 21869891
You're right.  It should work.  But, I ended up having to do this to get it to work...

 '" & Year(LastMonth) & "-" & Month(LastMonth) & "-" & Microsoft.VisualBasic.DateAndTime.Day(LastMonth) & "' )

Thanks!
0
 

Author Closing Comment

by:stephaniem712
ID: 31470661
Great help always!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

879 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