19 Replies Latest reply on Dec 21, 2017 12:17 PM by John Messinger

    Date difference from ODBC value

    Timur

      Hi all,

       

      I have ODBC Tag value which is giving a Date&Time value. I want to create a logic to calculate how many days passed since Date&Time value.

       

      i.e.

      Date&Time ODBC Tag >>>> 'LAST-ACCEPTED-WT.LAST_UPDATED_DATE' and giving a date and time: 06 - Nov -17 09:38:21 of last accepted well test date and time.

       

      I want 'Now'- 'LAST-ACCEPTED-WT.LAST_UPDATED_DATE' = .... days result, to know how many days past since last accepted well test.

       

      Appreciate your help.

       

      Thanks

       

      Timur

        • Re: Date difference from ODBC value
          Kenji Hashimoto

          Analysis's Expression can be used. If your attribute contains DateTime value then try following syntax.

          Int('*' - 'attribute name')/86400

          1 of 1 people found this helpful
            • Re: Date difference from ODBC value
              Timur

              Hi Kenji,

               

              Is there any way to do it via VBA?

               

              Thanks

                • Re: Date difference from ODBC value
                  John Messinger

                  In VBA you can do this using the DateDiff() function.

                   

                  John

                    • Re: Date difference from ODBC value
                      Timur

                      Hi John,

                       

                      Can you please elaborate more?

                       

                       

                      I just need to have DAY difference between these 2 values.

                       

                      Please help me.

                        • Re: Date difference from ODBC value
                          John Messinger

                          Timur,

                           

                          Did you look at the link I provided in my previous reply? If so, you would have seen a very simple code example using the DateDiff() function that you should be able to easily adapt to your ProcessBook display. You haven't mentioned anything about your skill level with VBA, so I will assume that by asking if this can be done in VBA that you have some level of skill/experience. You need simply convert the timestamp values in your two text boxes to Date variables, and then use the DateDiff() function to calculate the difference in days (as per the linked example).

                           

                          John

                            • Re: Date difference from ODBC value
                              Timur

                              Hi John

                               

                              I have used the link that you have provided as per below:

                               

                              Sub norm()

                              Dim rL As Date

                              Dim nL As Long

                              Dim TheDate As Date  

                              Dim x As PBObjLib.Dataset

                               

                              TheDate = Value1.GetValue(rL, nL)

                               

                              Set x = Application.ActiveDisplay.Datasets.GetDataset("DIFF")

                              x.Expression = DateDiff("d", Now, TheDate)

                              Application.ActiveDisplay.Datasets.SetDataset x

                               

                              End Sub

                               

                               

                              Value1 data format  is:

                               

                              But no matter what date it is the result giving: 43077.

                               

                               

                              PLease advise

                                • Re: Date difference from ODBC value
                                  John Messinger

                                  Hi Timur,

                                   

                                  So it looks like your Date variable is being set from a call to the GetValue() method of the symbol displaying your date text. As a result, the value of your Date variable TheDate is being set to something like '12:00:00 AM', thus producing this odd result. What is the symbol type for this object? When I tested this here, I used a Text symbol, and used the Contents property to set a Date variable:

                                   

                                  Dim theDate As Date
                                  Dim result As Variant
                                          
                                  theDate = txtDateStart.Contents
                                  result = DateDiff("d", theDate, Now)
                                  lblResult.Caption = CStr(result)
                                  

                                   

                                  If you need to validate what the value of your TheDate variable is, try using a Debug.Print statement after setting the variable to see what the value is.

                                   

                                  Let us know how you go with this.

                                   

                                  John

                                    • Re: Date difference from ODBC value
                                      Timur

                                      Hi John,

                                       

                                      I have used Value to call date tag from ODBC. It is value format from properties checking.

                                       

                                      Output result in days also calling from dataset "DIFF" as a value format. No changes still showing that odd number.

                                       

                                      Sub norm()

                                      Dim rL As Date

                                      Dim nL As Long

                                      Dim TheDate As Date   ' Declare variables.

                                      Dim x As PBObjLib.Dataset

                                      Dim result As Variant

                                       

                                      TheDate = Value1.GetValue(rL, nL)

                                      result = DateDiff("d", TheDate, Now)

                                       

                                      Set x = Application.ActiveDisplay.Datasets.GetDataset("DIFF")

                                      x.Expression = result

                                      Application.ActiveDisplay.Datasets.SetDataset x

                                       

                                      End Sub

                                        • Re: Date difference from ODBC value
                                          John Messinger

                                          Can you try casting the value to a string (so you end up with an actual string representation of the date) before assigning it to the Date variable?

                                            • Re: Date difference from ODBC value
                                              Timur

                                              Hi John

                                               

                                              Can you please elaborate more on that? I m not that familiar with that.

                                               

                                              Appreciate if you can make a correction of my vba code.

                                               

                                              Thanks

                                                • Re: Date difference from ODBC value
                                                  John Messinger

                                                  Casting (or converting) to a string in VBA is done with the CStr() function. With your code, I would probably use an intermediate variable to do this, as follows:

                                                   

                                                  Sub norm()
                                                  Dim rL As Date
                                                  Dim nL As Long
                                                  Dim TheDate As Date   ' Declare variables.
                                                  Dim x As PBObjLib.Dataset
                                                  Dim result As Variant
                                                  Dim strDate As String
                                                  
                                                  strDate = CStr(Value1.GetValue(rL, nL))
                                                  TheDate = CDate(strDate)
                                                  result = DateDiff("d", TheDate, Now)
                                                  
                                                  Set x = Application.ActiveDisplay.Datasets.GetDataset("DIFF")
                                                  x.Expression = result
                                                  Application.ActiveDisplay.Datasets.SetDataset x
                                                  
                                                  End Sub
                                                  

                                                   

                                                  John

                                                    • Re: Date difference from ODBC value
                                                      Timur

                                                      Hi John

                                                       

                                                      The code above says that Type Mismatch error on:

                                                       

                                                      11. result = DateDiff("d", TheDate, Now) 

                                                       

                                                      Please help me

                                                        • Re: Date difference from ODBC value
                                                          John Messinger

                                                          You need to add some debug statements to validate the results of your type conversions. Try the following, and note the Debug.Print statements:

                                                           

                                                          Sub norm()  
                                                               Dim rL As Date  
                                                               Dim nL As Long  
                                                               Dim TheDate As Date   ' Declare variables.  
                                                               Dim x As PBObjLib.Dataset
                                                               Dim theValue as Variant
                                                               Dim result As Variant  
                                                               Dim strDate As String  
                                                          
                                                               theValue = Value1.GetValue(rL, nL)  
                                                               Debug.Print theValue
                                                               strDate = CStr(theValue)  
                                                               Debug.Print strDate
                                                               TheDate = CDate(strDate)  
                                                               Debug.Print TheDate
                                                               result = DateDiff("d", TheDate, Now)  
                                                          
                                                               Set x = Application.ActiveDisplay.Datasets.GetDataset("DIFF")  
                                                               x.Expression = result  
                                                               Application.ActiveDisplay.Datasets.SetDataset x  
                                                            
                                                          End Sub
                                                          

                                                           

                                                          The Type mismatch errors you saw indicate that the type conversion failed, so the above debug statements are a way for you to see what is happening at each step, and what the variable values are that are being converted to other data types. You need to open the Intermediate window in the Visual Basic code editor to see the output of the Debug.Print statements.

                                                          1 of 1 people found this helpful
                                                            • Re: Date difference from ODBC value
                                                              Timur

                                                              Hi John,

                                                               

                                                              Unfortunately still error message persist:

                                                               

                                                               

                                                              After line 15 popup this error message again.

                                                              15.     Debug.Print TheDate 

                                                               

                                                              So trouble to make a simple time lapse calculation.

                                                               

                                                              Immediate Window showing these messages:

                                                               

                                                               

                                                               

                                                                • Re: Date difference from ODBC value
                                                                  John Messinger

                                                                  OK, so this tells you that the value of the variable called theValue (which was set on line 10) resolves to '12:00:00 AM', which is not a valid DateTime representation. It also accounts for the reason you are seeing a Type mismatch error, as this string can't be converted to a valid DateTime type.

                                                                   

                                                                  So, normally when you call the GetValue() method of a Symbol such as a Value, the actual value of the symbol is returned as a Variant type, meaning it can hold almost anything. Out of curiosity, what is the underlying column type in your ODBC data source? Database type would be useful to know here as well. Are you also able to share the SQL query that populates your ODBC dataset?

                                                                   

                                                                  The other thing I'm seeing here that is potentially problematic is the last three lines of your code:

                                                                   

                                                                   

                                                                  This may cause you problems as well - why are you trying to set the Expression property of a PIExpression dataset with the value of the 'result' variable? Do you need to use this value in other symbols? Why not just directly set the value of a Text object?