DateAdd Datediff Speed

[LeandroA]

Hi, I'm looking for speed and these DateAdd and DateDiff functions don't help me, I've seen that varDate + 1 is much faster than DateAdd("d",1,VarDate) is there any contraindication for that??
same with DateDiff date

'If DateDiff("d", varDate1, .varDate2) < 0 Then <----Slow
'If Day(.varDate1) < Day(varDate2) Then <----a little faster
If varDate1 < varDate2 Then <----BEST!

I assume this has no conflict?

[Arnoutdv]

Day will return the day of the month only.
So if you compare the 14th of September with the 27th of January then you get not the desired result.

Date variables are just doubles, with in the integer part the date and in the fraction the time.
So A < B is will do what you want. This faster than calling DateDiff because DateDiff is a call to a function which has to parse the parameters specified and then do the comparison.

[dilettante]

DateDiff() does a lot of work.

[Eduardo-]

Hi, I'm looking for speed and these DateAdd and DateDiff functions don't help me, I've seen that varDate + 1 is much faster than DateAdd("d",1,VarDate) is there any contraindication for that??
same with DateDiff date

'If DateDiff("d", varDate1, .varDate2) < 0 Then <----Slow
'If Day(.varDate1) < Day(varDate2) Then <----a little faster
If varDate1 < varDate2 Then <----BEST!

I assume this has no conflict?

Hello Leandro. I think that if the operations are adding or subtracting days, and the comparison are simple, that should work without issues.

[Elroy]

Be careful though with varDate1 < varDate2. If varDate2 is the same day, just later in the day, that test will return true. If you're wanting to just compare days (no time), you might try this, which should be faster than all of them except for varDate1 < varDate2.

If Int(varDate1) < Int(varDate2) Then ...

[dilettante]

Since the Date type contains both date and time by definition that seems like a weird thing to have to caution somebody about. But anyway:

https://www.aivosto.com/articles/datatypes.html

Expressions for VB6 date/time

Good expressions:
Get date without time: Fix(T)
Get time without date: Abs(T-Fix(T))
Get time difference in days: DateDiff("s", T1, T2)/86400

Avoid these:
Get date without time: Int(T) – Wrong for historical dates
Get time without date: T-Int(T) – Wrong for historical dates
Get time difference in days: T2-T1 – Wrong for historical dates with time

Bug with DateDiff: DateDiff("s", -0.5, 0.5) = 1 second, even though it should be 0 seconds.

[Eduardo-]

Get time difference in days: T2-T1 – Wrong for historical dates with time

It would be nice to know exactly that issue, better with an example, because perhaps if it is about dates in 1900, it is not an issue for the OP.

PS: and dates frequently don't have or need the time part.

[dilettante]

Read the article. It is explained there.

[Eduardo-]

The integral part is the date, the fraction is the time: date.time. That's easy. Things get odd when the value is negative. This is on or before #12/30/1899#.

With modern dates time always runs forwards, as you would suspect. With negative historical dates...

As I suspected: there is no problem with normal dates, only with dates near 1900.
So it depends on what the OP is doing.
If he needs speed, and work with dates for example year 2000+, or without time, there is no reason to use much slower functions.

What kind of program would need to use data like February 25, 1898, 7:05 PM?

[dilettante]

Why do it right for zero effort and zero (or nearly zero) cost when doing it wrong won't blow up in your face most of the time? You have been warned.

[PlausiblyDamp]

Hi, I'm looking for speed and these DateAdd and DateDiff functions don't help me, I've seen that varDate + 1 is much faster than DateAdd("d",1,VarDate) is there any contraindication for that??
same with DateDiff date

'If DateDiff("d", varDate1, .varDate2) < 0 Then <----Slow
'If Day(.varDate1) < Day(varDate2) Then <----a little faster
If varDate1 < varDate2 Then <----BEST!

I assume this has no conflict?

Just out of interest - have you done any performance benchmarking on these approaches? I am honestly curious about how big a difference the different techniques make in practice.

[Eduardo-]

Why do it right for zero effort and zero (or nearly zero) cost when doing it wrong won't blow up in your face most of the time? You have been warned.

Haw you read the OP? He needs speed.

[Elroy]

Good catch Dil.

I guess I should have said:

If Fix(varDate1) < Fix(varDate2) Then ...

That would cover all cases, including those with dates prior to 1900 (i.e., negative dates).

[dilettante]

Haw you read the OP? He needs speed.

Does he? Does he really?

I'll bet he hasn't even run a code profiler to see where his bottlenecks are. Of course DateDiff() is the wrong choice, but that's pretty obvious.

[Eduardo-]

Does he? Does he really?

Hi, I'm looking for speed

._._

[LeandroA]

Thank for all advice, if the only con is because of that difference from the year 1900, it would not cause me any inconvenience, it is for a control of the calendar of events. I think I've already been able to speed it up a lot.

I got good tips from this post

[Elroy]

Bottom line ... Date types are just a special-use-case of Double types. And, as such, you can treat them exactly like Double types. In fact, when you add or subtract them, it'll send them straight to the CPU's floating-point-processor, which is extremely fast. And who knows what all that DateDiff and the other date functions are doing. (We absolutely know that it's casting the input dates into Variants, and who knows what else.) Sure, sometimes you need them. But, often you don't.