Solved

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

Posted on 2008-06-25
12
1,445 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
 

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 500 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

19 Experts available now in Live!

Get 1:1 Help Now