Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-25
12
Medium Priority
?
1,537 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
[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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

704 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