We help IT Professionals succeed at work.

DateDiff function

edonasan
edonasan asked
on
Hi,

I have a problem with the DateDiff function in VB6.

I compare with 2 dates to get the date difference. (dd-mm-yyyy)
eg. 1-12-2001 and 2-12-2001
*******************************************************

Scenario 1:

Date format in Regional Settings is dd-MM-yyyy.

Result = DateDiff("d", "1-12-2001", "2-12-2001")

and i get my result = 1, meaning 1 day.


Scenario 2:

Date format in Regional Settings is MM-dd-yyyy.

Result = DateDiff("d", "1-12-2001", "2-12-2001")

and i get my result = 30, meaning 30 days.

******************************************************

how can i get the format of the system date so as to format the suitable date format for the function to work correctly?

how shd i be independant of the Regional Settings date format to get the correct DateDiff result?

or any suggestion to resolve this problem?


Comment
Watch Question

TimCotteeHead of Software Services
CERTIFIED EXPERT

Commented:
Always use something that cannot be ambiguous:

Format(Date,"yyyy-mm-dd") or Format(Date,"dd mmm yyyy")

This will give 2001-12-01 or 01 Dec 2001 which cannot be confused. So take the dates and convert to either of these formats and then use this in the datediff. You should always then get the correct result.
CERTIFIED EXPERT
Commented:
Hi
You can use DateSerial to 'hardcoded' date and convert it to system format:
Date1 = DateSerial(2001,12,1)
Date1 = DateSerial(2001,12,2)
Result = DateDiff("d",Date1,Date2)

Result won't depend on regional settings.

Cheers
Ark
CERTIFIED EXPERT

Commented:
PS. To get values for DateSerial, use Split function if your DateString is always in dd-mm-yyyy format:
DateArr = Split(DateString,"-")
Date1 = DateSerial(DateArr(2),DateArr(1),DateArr(0))

Cheers
TimCottee's suggestion works well, something like...

dayCount = datediff("d", format(date1, "dd/mm/yyyy"), format(date2, "dd/mm/yyyy"))


Author

Commented:
hi Ark,

I have learnt something new on DateSerial function, thanks!

Author

Commented:
I have tried TimCottee's suggestion,
it didn't work when the regional settings for the short date is MM/dd/yyyy.

Thanks anyway :)

Ark
CERTIFIED EXPERT

Commented:
Thanks for points, glad I could help