dcsimg
Results 1 to 10 of 10

Thread: [RESOLVED] Issue with DateDiff function

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    5

    Resolved [RESOLVED] Issue with DateDiff function

    Just installed VS 2019. Imported code from VS 2017. DateDiff function does not return the number of days when using a variable. If I test with the actual value, then I get number of days.

    Example:

    Datediff(DateInterval.day, #6/1/2019#, #6/27/2019#) returns the correct value

    If I use Datediff(dateinterval.day, firstdate, seconddate) where firstdate is a variable defied as date with value #6/1/2019# and seconddate is a variable defined as date with a value #6/27/2019# then the result is the tick value of the difference.

    The function worked in Vs 2017 and VS2010.

    Here is the code:

    Protected Sub btnAgeInvoices_Click(sender As Object, e As EventArgs) Handles btnAgeInvoices.Click
    Dim MyDelete As String = "Delete From Agereport"
    Dim objConnection As SqlConnection
    Dim objReader As SqlDataReader
    Dim MyInsert As String, MyUpdate As String, MyQuery As String
    Dim strErrorMessage As String = "", EndDate As String, intDaysLate As Integer = 0
    PageError.Visible = False
    Label1.Visible = False
    PageMessage.Visible = False
    strErrorMessage = UpdateRecordSet(MyDelete, Session("UserName"))
    If strErrorMessage <> "" Then
    Label1.Visible = True
    PageMessage.Visible = True
    PageMessage.Text = strErrorMessage
    Exit Sub
    End If
    objConnection = getDbConnection()
    MyInsert = "Insert InTo [AgeReport] ([Carrier Code], [Invoice Number], Name, [Carrier case number], [Invoice Due Date], [WCB Number], [Date of accident], [Amt Current], "
    MyInsert += "[Invoice Amt], [Pay Amount], [Bill Type], [Case Code], [Hearing Seq], [Writeoff Amt], [Case Against], [Check Number]) "
    MyInsert += "Select [BillingDetail].[Carrier Code], [BillingDetail].[Invoice Number], Substring(Replace([Case].[Name1], '''', ''''''), 1, 50) As Name, [Case].[Carrier case number], [BillingDetail].[Invoice Due Date], "
    MyInsert += "[Case].[WCB Number], [Case].[Date Of Accident], [BillingDetail].[Invoice Balance] As [Amt Current], [BillingDetail].[Invoice Amt], "
    MyInsert += "[BillingDetail].[Pay Amount], [BillingDetail].[Bill Type], [BillingDetail].[Case Code], [BillingDetail].[Hearing Seq], "
    MyInsert += "[BillingDetail].[Writeoff Amt], Replace([Case Against], '''', ''''''), [Check Number] "
    MyInsert += "From [BillingDetail], [Case] "
    MyInsert += "Where [BillingDetail].[Invoice Closed] = 0 AND [BillingDetail].[Invoice Billed] = 1 AND [BillingDetail].[Case Code] = [Case].[Case Code] "
    MyInsert += "And [BillingDetail].[Invoice Date] > '1/1/2015' AND [Invoice Amt] <> 0.00"
    strErrorMessage = UpdateRecordSet(MyInsert, Session("UserName"))
    If strErrorMessage <> "" Then
    Label1.Visible = True
    PageMessage.Visible = True
    PageMessage.Text = strErrorMessage & " SQL = " & MyInsert
    Exit Sub
    End If
    MyUpdate = "Update [AgeReport] Set [AgeReport].[Hearing Date] = RAN.[Hearing Date] From AgeReport AR"
    MyUpdate += " Inner Join [Hearing] RAN On RAN.[Case Code] = AR.[Case Code] AND RAN.[Hearing Seq] = AR.[Hearing Seq] AND AR.[Bill Type] = 'RH'"
    strErrorMessage = UpdateRecordSet(MyUpdate, Session("UserName"))
    If strErrorMessage <> "" Then
    Label1.Visible = True
    PageMessage.Visible = True
    PageMessage.Text = strErrorMessage
    Exit Sub
    End If
    MyQuery = "Select * From Company"
    objReader = getDbResultSet(objConnection, MyQuery)
    objReader.Read()
    EndDate = objReader("Current Date")
    objReader.Close()
    MyQuery = "Select * From AgeReport"
    objReader = getDbResultSet(objConnection, MyQuery)
    Dim dteEndDate As Date = "#" & EndDate & "#"
    Dim dteInvoiceDueDate As Date
    While objReader.Read
    dteInvoiceDueDate = "#" & objReader("Invoice Due Date") & "#"
    intDaysLate = DateDiff("d", dteInvoiceDueDate, dteEndDate)
    MyUpdate = "Update AgeReport Set [Days Late] = " & intDaysLate
    Select Case intDaysLate
    Case Is < 31
    Case Is < 61
    MyUpdate += ", [Amt 30] = " & objReader("Amt Current") & ", [Amt Current] = 0"
    Case Is < 91
    MyUpdate += ", [Amt 60] = " & objReader("Amt Current") & ", [Amt Current] = 0"
    Case Is < 121
    MyUpdate += ", [Amt 90] = " & objReader("Amt Current") & ", [Amt Current] = 0"
    Case Is > 120
    MyUpdate += ", [Amt 120] = " & objReader("Amt Current") & ", [Amt Current] = 0"
    End Select
    MyUpdate += " Where [Carrier Code] = '" & objReader("Carrier Code") & "' AND [Invoice Number] = '" & objReader("Invoice Number") & "'"
    strErrorMessage = UpdateRecordSet(MyUpdate, Session("UserName"))
    If strErrorMessage <> "" Then
    Label1.Visible = True
    PageMessage.Visible = True
    PageMessage.Text = strErrorMessage & "SQL = " & MyUpdate
    Exit Sub
    End If
    End While
    objReader.Close()
    objConnection.Close()
    PageError.Visible = True
    PageError.Text = "Age report data has been created"
    End Sub
    Last edited by Ilandau; Jun 28th, 2019 at 04:14 PM.

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,948

    Re: Issue with DateDiff function

    Welcome to VBForums

    The most likely issue is that somehow the values of the variables aren't quite what you think they are (as it is surprisingly easy to write date-based code that changes behaviour based on things outside the code), if you show us the code you are using then we can probably work out what is going wrong, or at least suggest changes that are likely to fix it.

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    5

    Re: Issue with DateDiff function

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    The most likely issue is that somehow the values of the variables aren't quite what you think they are (as it is surprisingly easy to write date-based code that changes behaviour based on things outside the code), if you show us the code you are using then we can probably work out what is going wrong, or at least suggest changes that are likely to fix it.
    Here is the code:
    Code:
            MyQuery = "Select * From Company"
            objReader = getDbResultSet(objConnection, MyQuery)
            objReader.Read()
            EndDate = objReader("Current Date")
            objReader.Close()
            MyQuery = "Select * From AgeReport"
            objReader = getDbResultSet(objConnection, MyQuery)
            Dim dteEndDate As Date = "#" & EndDate & "#"
            Dim dteInvoiceDueDate As Date
            While objReader.Read
                dteInvoiceDueDate = "#" & objReader("Invoice Due Date") & "#"
                intDaysLate = DateDiff("d", dteInvoiceDueDate, dteEndDate)
                MyUpdate = "Update AgeReport Set [Days Late] = " & intDaysLate
                Select Case intDaysLate
                    Case Is < 31
                    Case Is < 61
                        MyUpdate += ", [Amt 30] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is < 91
                        MyUpdate += ", [Amt 60] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is < 121
                        MyUpdate += ", [Amt 90] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is > 120
                        MyUpdate += ", [Amt 120] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                End Select
    Last edited by si_the_geek; Jun 28th, 2019 at 04:38 PM. Reason: added Code tags

  4. #4

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    5

    Re: Issue with DateDiff function

    Quote Originally Posted by si_the_geek View Post
    Welcome to VBForums

    The most likely issue is that somehow the values of the variables aren't quite what you think they are (as it is surprisingly easy to write date-based code that changes behaviour based on things outside the code), if you show us the code you are using then we can probably work out what is going wrong, or at least suggest changes that are likely to fix it.
    Here is the code:
    Code:
        Protected Sub btnAgeInvoices_Click(sender As Object, e As EventArgs) Handles btnAgeInvoices.Click
            Dim MyDelete As String = "Delete From Agereport"
            Dim objConnection As SqlConnection
            Dim objReader As SqlDataReader
            Dim MyInsert As String, MyUpdate As String, MyQuery As String
            Dim strErrorMessage As String = "", EndDate As String, intDaysLate As Integer = 0
            PageError.Visible = False
            Label1.Visible = False
            PageMessage.Visible = False
            strErrorMessage = UpdateRecordSet(MyDelete, Session("UserName"))
            If strErrorMessage <> "" Then
                Label1.Visible = True
                PageMessage.Visible = True
                PageMessage.Text = strErrorMessage
                Exit Sub
            End If
            objConnection = getDbConnection()
            MyInsert = "Insert InTo [AgeReport] ([Carrier Code], [Invoice Number], Name, [Carrier case number], [Invoice Due Date], [WCB Number], [Date of accident], [Amt Current], "
            MyInsert += "[Invoice Amt], [Pay Amount], [Bill Type], [Case Code], [Hearing Seq], [Writeoff Amt], [Case Against], [Check Number]) "
            MyInsert += "Select [BillingDetail].[Carrier Code], [BillingDetail].[Invoice Number], Substring(Replace([Case].[Name1], '''', ''''''), 1, 50) As Name, [Case].[Carrier case number], [BillingDetail].[Invoice Due Date], "
            MyInsert += "[Case].[WCB Number], [Case].[Date Of Accident], [BillingDetail].[Invoice Balance] As [Amt Current], [BillingDetail].[Invoice Amt], "
            MyInsert += "[BillingDetail].[Pay Amount], [BillingDetail].[Bill Type], [BillingDetail].[Case Code], [BillingDetail].[Hearing Seq], "
            MyInsert += "[BillingDetail].[Writeoff Amt], Replace([Case Against], '''', ''''''), [Check Number] "
            MyInsert += "From [BillingDetail], [Case] "
            MyInsert += "Where [BillingDetail].[Invoice Closed] = 0 AND [BillingDetail].[Invoice Billed] = 1 AND [BillingDetail].[Case Code] = [Case].[Case Code] "
            MyInsert += "And [BillingDetail].[Invoice Date] > '1/1/2015' AND [Invoice Amt] <> 0.00"
            strErrorMessage = UpdateRecordSet(MyInsert, Session("UserName"))
            If strErrorMessage <> "" Then
                Label1.Visible = True
                PageMessage.Visible = True
                PageMessage.Text = strErrorMessage & " SQL = " & MyInsert
                Exit Sub
            End If
            MyUpdate = "Update [AgeReport] Set [AgeReport].[Hearing Date] = RAN.[Hearing Date] From AgeReport AR"
            MyUpdate += " Inner Join [Hearing] RAN On RAN.[Case Code] = AR.[Case Code] AND RAN.[Hearing Seq] = AR.[Hearing Seq] AND AR.[Bill Type] = 'RH'"
            strErrorMessage = UpdateRecordSet(MyUpdate, Session("UserName"))
            If strErrorMessage <> "" Then
                Label1.Visible = True
                PageMessage.Visible = True
                PageMessage.Text = strErrorMessage
                Exit Sub
            End If
            MyQuery = "Select * From Company"
            objReader = getDbResultSet(objConnection, MyQuery)
            objReader.Read()
            EndDate = objReader("Current Date")
            objReader.Close()
            MyQuery = "Select * From AgeReport"
            objReader = getDbResultSet(objConnection, MyQuery)
            Dim dteEndDate As Date = "#" & EndDate & "#"
            Dim dteInvoiceDueDate As Date
            While objReader.Read
                dteInvoiceDueDate = "#" & objReader("Invoice Due Date") & "#"
                intDaysLate = DateDiff("d", dteInvoiceDueDate, dteEndDate)
                MyUpdate = "Update AgeReport Set [Days Late] = " & intDaysLate
                Select Case intDaysLate
                    Case Is < 31
                    Case Is < 61
                        MyUpdate += ", [Amt 30] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is < 91
                        MyUpdate += ", [Amt 60] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is < 121
                        MyUpdate += ", [Amt 90] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                    Case Is > 120
                        MyUpdate += ", [Amt 120] = " & objReader("Amt Current") & ", [Amt Current] = 0"
                End Select
                MyUpdate += " Where [Carrier Code] = '" & objReader("Carrier Code") & "' AND [Invoice Number] = '" & objReader("Invoice Number") & "'"
                strErrorMessage = UpdateRecordSet(MyUpdate, Session("UserName"))
                If strErrorMessage <> "" Then
                    Label1.Visible = True
                    PageMessage.Visible = True
                    PageMessage.Text = strErrorMessage & "SQL = " & MyUpdate
                    Exit Sub
                End If
            End While
            objReader.Close()
            objConnection.Close()
            PageError.Visible = True
            PageError.Text = "Age report data has been created"
        End Sub
    Last edited by si_the_geek; Jun 28th, 2019 at 04:38 PM. Reason: added Code tags

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,948

    Re: Issue with DateDiff function

    Ah yes, there is something there like I suspected I'm afraid.

    This part shows the problem well:
    Quote Originally Posted by Ilandau View Post
    Code:
            EndDate = objReader("Current Date")
            ...
            Dim dteEndDate As Date = "#" & EndDate & "#"
    I'm not sure what data type the value from the reader will be, but assuming the database field has a Date based data type then it will probably work. If EndDate is a date based data type too it would be better with CDate around it, eg:
    Code:
            EndDate = CDate(objReader("Current Date"))
    As for the other line, that is very problematic, and the behaviour is not something I can predict (except that the month and day are likely to be swapped, based on things outside of your code).

    Putting hash marks into strings to append them to a value does not mean that the value between them will be treated as you intended, and you also can't be sure what format will be used when EndDate is converted to a string.

    Assuming that EndDate is a Date variable, it should be like this:
    Code:
            Dim dteEndDate As Date = EndDate
    ..but if it is string based then CDate should be used at this point:
    Code:
            Dim dteEndDate As Date = CDate(EndDate)
    Changing these things (for the lines I quoted, and others like them) might not solve it completely, but they are at least a step in the right direction.

  6. #6

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    5

    Re: Issue with DateDiff function

    Quote Originally Posted by si_the_geek View Post
    Ah yes, there is something there like I suspected I'm afraid.

    This part shows the problem well:
    I'm not sure what data type the value from the reader will be, but assuming the database field has a Date based data type then it will probably work. If EndDate is a date based data type too it would be better with CDate around it, eg:
    Code:
            EndDate = CDate(objReader("Current Date"))
    As for the other line, that is very problematic, and the behaviour is not something I can predict (except that the month and day are likely to be swapped, based on things outside of your code).

    Putting hash marks into strings to append them to a value does not mean that the value between them will be treated as you intended, and you also can't be sure what format will be used when EndDate is converted to a string.

    Assuming that EndDate is a Date variable, it should be like this:
    Code:
            Dim dteEndDate As Date = EndDate
    ..but if it is string based then CDate should be used at this point:
    Code:
            Dim dteEndDate As Date = CDate(EndDate)
    Changing these things (for the lines I quoted, and others like them) might not solve it completely, but they are at least a step in the right direction.


    I set both variables to type date and ran the process using DateDiff("d" and DateDiff(DateInterval.Day I have attached the results of both runs. The changes did not work. Still getting same result.
    Attached Files Attached Files

  7. #7
    Frenzied Member PlausiblyDamp's Avatar
    Join Date
    Dec 2016
    Location
    Newport, UK
    Posts
    1,048

    Re: Issue with DateDiff function

    May not help much in this situation but if you are using VB.Net it might be easier and clearer to use the built in date functionality rather than the legacy VB6 methods.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,948

    Re: Issue with DateDiff function

    Quote Originally Posted by Ilandau View Post
    I set both variables to type date and ran the process using DateDiff("d" and DateDiff(DateInterval.Day I have attached the results of both runs. The changes did not work. Still getting same result.
    The watch window in your screenshot contains six items, and three of them are directly related to this.

    At a glance the value of intDaysLate is about right, given that the two dates are about 1000 years apart.


    If you weren't expecting there to be a date 1000 years in the future, think about why it might be happening. In this case think about exactly where the value is coming from, and why that might be a problem. You are getting data from a database table, but the query is far too basic (you should be specifying the fields you want returned, and be using a Where clause). It also isn't clear to us if the database field has an appropriate data type.

  9. #9
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,948

    Re: Issue with DateDiff function

    Quote Originally Posted by Ilandau View Post
    I set both variables to type date and ran the process using DateDiff("d" and DateDiff(DateInterval.Day I have attached the results of both runs. The changes did not work. Still getting same result.
    The watch window in your screenshot contains six items, and three of them are directly related to this.

    At a glance the value of intDaysLate is about right, given that the two dates are about 1000 years apart.


    If you weren't expecting there to be a date 1000 years in the future, think about why it might be happening. In this case think about exactly where the value is coming from, and why that might be a problem. You are getting data from a database table, but the query is far too basic (you should be specifying the fields you want returned, and be using a Where clause). It also isn't clear to us if the database field has an appropriate data type.

  10. #10

    Thread Starter
    New Member
    Join Date
    Jun 2019
    Posts
    5

    Re: Issue with DateDiff function

    Thanks, I did not catch the incorrect date in the end date variable. Process works correctly.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width