Confusion when comparing dates

Question

I'm having a problem when comparing dates:

Sub Main()


    Dim xlApp, xlApp1, xlApp2 As Excel.Application
    Dim xlWorkBook, xlWorkBook1, xlWorkBook2 As Excel.Workbook
    Dim xlWorkSheet, xlWorkSheet1, xlWorkSheet2 As Excel.Worksheet
    Dim folder, m, n As String
    Dim subfolders As String()
    Dim i, j, c, k, l, lastrow As Integer
    Dim fec,temp As Date
    Dim nulls As Boolean

    nulos = False
    folder = My.Application.Info.DirectoryPath
    ChDir(CurDir())
    subfolders = IO.Directory.GetDirectories(CurDir())
    xlApp = New Excel.ApplicationClass
    xlApp2 = New Excel.ApplicationClass
    xlApp1 = New Excel.ApplicationClass

    Try

        xlWorkBook = xlApp.Workbooks.Open("d:\File where data will be copied.xlsx")
        xlWorkSheet = xlWorkBook.Worksheets("SheetName")
        xlWorkSheet.Activate()
        xlWorkBook2 = xlApp2.Workbooks.Open("d:\File Where Dates Are Recorded.xlsx")
        xlWorkSheet2 = xlWorkBook2.Worksheets("SheetName")
        xlWorkSheet2.Activate()
        i = 2

        For Each f1 In subfolders
            ChDir(f1)
            m = Dir("*.xlsx")

            Do While m <> ""

                If Strings.Left(m, 6) = "DOC_ID" Then
                    j = 2

                    Do While xlWorkSheet2.Cells(j, 1).Value <> ""

                        If xlWorkSheet2.Cells(j, 1).Value = m Then
                            fec = xlWorkSheet2.Cells(j, 2).value

                            'Check if last write date is the same as the one recorded in the file
                            temp = File.GetLastWriteTime(CurDir() & "\" & m)
                            If fec <> File.GetLastWriteTime(CurDir() & "\" & m) Then

                            .
                            .
                            .

In the last line

If fec <> File.GetLastWriteTime(CurDir() & "\" & m)

I'm comparing the modified date of a file with a date stored inside a worksheet cell as date format. When debugging, fec and temp have the same value(already tested in debugger) but It still enters inside the if condition...Why? Any ideas?

Values in locals window:

fec #11/15/2013 6:06:01 PM#

temp #11/15/2013 6:06:01 PM#

note that the line `Dim fec,temp As Date` declares fec as a **variant** and temp as a **date**, because excel requires the type declaration after **each** variable. — SeanC, Nov 25 '13 at 18:48

score 2 · Answer 1 · answered Nov 25 '13 at 16:02

2

Try using this code instead:

if DateTime.Compare(fec, temp) <> 0 then

Comparing certain values (such as strings and dates) usually works better using the built-in functions associated with the data type than using the '=' operator. See also: http://msdn.microsoft.com/en-us/library/system.datetime.compare(v=vs.110).aspx

answered Nov 25 '13 at 16:02

steenbergh

1,642
3
22
40

1

Since DateTime.op_Equality and DateTime.Compare are implemented similarly (according to ILSpy), I doubt that this will make a difference. – Heinzi Nov 25 '13 at 16:05
1

Maybe not, but the (int) result of Compare might give more information (into the rounding aspect) as to which is considered larger than the other, i.e. which has a microsecond part set and which has a zero ms component.. – steenbergh Nov 25 '13 at 16:16
How to make visual studio ignore(truncate?) any time unit smaller than 1 second? – phalanx Nov 25 '13 at 16:42
1

They both compare the value of a property called InternalTicks. The Compare method simply returns 1, 0, or -1 depending on whether the first date is greater, equal, or less than the second. Otherwise they should be identical. – Chris Dunaway Nov 25 '13 at 16:43
DateTime.Compare(fec, temp) = -1. Temp is considered higher. – phalanx Nov 25 '13 at 16:55
1

I found this piece of code: `temp = temp.AddMilliseconds(-temp.Millisecond)` This alters the temp variable to drop its millisecond component (or more accurately, it creates a new date by taking temp as a base and then subtracting temp's own milliseconds from it). It's C# code, bu should work for you too. Found it here: [link]http://stackoverflow.com/questions/7028930/ignore-milliseconds-when-comparing-two-datetimes[/link] – steenbergh Nov 25 '13 at 20:58

score 2 · Accepted Answer · answered Nov 25 '13 at 16:12

2

= and <> basically just compare DateTime.Ticks. You might want to output fec.Ticks and File.GetLastWriteTime(...).Ticks for debugging purposes. A millisecond equals 10,000 ticks.

If they are different by a multiple of a full hour, you might want to check DateTime.Kind. It's possible that one is in UTC and the other one in your local time zone. If that is the case, you need to compensate for the difference.

answered Nov 25 '13 at 16:12

Heinzi

167,459
57
363
519

My god.. Well I'm not testing if Higgs particle is faster than speed of light, so I'm gonna try to convert both dates to string. I just want to compare seconds(ignoring milliseconds,ticks or any smaller time unit) – phalanx Nov 25 '13 at 16:38

Confusion when comparing dates

2 Answers2