Datediff and regional settings problem 2

[newora]

Hi there,

Could someone please help me with a problem that I thought would be very simple to solve but which is actually turning out to be a real pain and it must be my lack of understanding.

I have a function that basically determines the number of minutes between 2 passed dates. This works fine on my clients systems (regional settings doe date are US format)but does not work correctly on my systems which have the regional setting for dates to be UK format.

I obviously wanted to develop the software (the program is actually called from a system service) so that It would be independant of the actual regional settings of the PC it was running on and so decided to always work in American date format within VB (i.e. MM/DD/YYYY HH:NN:SS).

Thus, the code gets 2 dates from a database table and then uses the format function to put them into MM/DD/YYYY hh:nn:ss format. I then use cdate() to convert the strings into a date variable. These 2 date variable are then passed to the comparison function.

The problem that I am having is that when the software runs on my systems, using a UK date format, the datediff function is using the UK regional settings and so if the 2 dates from the database table have an a difference of 1 day, the datedfiff function thinks there is actaully a difference of 1 month!!

Thus if I pass the function the dates of (in US format):-
06/07/2007 23:45:00
and
06/08/2007 00:45:00

There is a difference of 60 minutes between the above 2 dates/times but the datediff function returns a number which is the number of minutes difference in the month. Thus it is obviously using the middle 2 digits for the month.

I have tried passing the dates as date literals and putting # around them, so that the system will always see the date in US format, but when I do this I get a type mismatch error, as I suppose by adding the "#" onto each end of the cdate() fucntion I am changing it back into a string?

This is what I tried :-

dim first_date as date
dim second_date as date

' dbdate1 and dbdsate2 reference database table collection fields
first_date = "#" & cdate(format(dbdate1),"MM/DD/YYYY hh:nn:ss") & "#"

second_date = "#" & cdate(format(dbdate2),"MM/DD/YYYY hh:nn:ss") & "#"

minutes_difference = get_minutes_difference(date1 as date, date2 as date)

end

private function get_minutes_difference(date1 as date,date2 as date)

get_minutes_difference = datediff(date1,date2)
return

This gives me a type msimatch error on the call to the get_minutes_difference function

Any pointers would be muich appreciated.
Thanks
Newora

 

[SkipVought]

Use an unambiguous date format in all cases...
[tt]
yyyy/mm/dd
[/tt]


Skip,
[sub]
[red][/red]
[/sub]

 

[Hypetia]

Values from Date fields of a database are returned as Variant/Date format. Therefore you should pass them directly to DateDiff function without converting to string format. DateDiff function will interpret them correctly regardless of date format in regional settings.
___
[tt]
'must be declared as Date (or Variant)
Dim dbDate1 As Date, dbDate2 As Date

dbDate1 = RS.Fields!Date1
dbDate2 = RS.Fields!Date2

minutes_difference = DateDiff("n", dbDate1, dbDate2)[/tt]
___

You can also call the DateDiff function indirectly using get_minutes_difference function (as you do in your code). Just make sure you always declare variables as Date and avoid assigning String values to Date variables.

 

[newora]

Thanks for the replies, but I'am still having difficulties and it is all to do with date values as opossed to strings.

I like the idea of using something unambiguous like yy/mm/dd but if I assign the following function call to a date variable then as the format function returns a string, it comes out in dd/mm/yyyy formaty (as per regional settings):-

dim sdate as date
dim str_date as string

' returns a date in the format dd/mm/yyyy regardless of format parameters
sdate = format(now(),"YYYY/MM/DD")
'returns a date of YYYY/MM/DD but as a string ie "2007/06/11"
str_date = format(now(),"YYYY/MM/DD")

If I use cdate to get the contents of variable str_date back to a date variable, it comes back to dd/mm/yyyy format,as that is what my regional settings are, I suppose

I really must be missing something here, but I just do not know what!!

Thanks again for the assistance.

 

[gmmastros]

>> Thus, the code gets 2 dates from a database table and then uses the format function to put them into MM/DD/YYYY hh:nn:ss format.

I think this is your problem. If you are storing the dates in the database using a datetime field, then you should not convert them to a string before returning them from the database. If you leave them as a date and handle them as dates in the VB code, there won't be any problem.

Additionally, most database engines have the ability to compare dates and return the time elapsed. You don't say what type of database you are using, but I know Microsoft Access and Microsoft SQL Server both have a DateDiff function. My point is that you may get more consisent results by returning this from the database.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Hypetia]

>sdate = format(now(),"YYYY/MM/DD")
>str_date = format(now(),"YYYY/MM/DD")

Why the heck are you using Format function to assign to your date variables? I said avoid assigning String values to Date variables. The Format function returns a String, and you are actually assigning a string to your date variable which spoils the date. Instead of a date->string->date conversion, use direct date-to-date assignment WITHOUT using Format function, like this:

sdate = Now
str_date = Now.

George is giving you the same advice in his post.

 

[HughLerwill]

Remember that a date variable wraps and actually stores the date in an underlying Double variable where numbers to the left of the decimal point represent the number of days (since a reference date, OTTOMH 01 Jan 1889) and those to the right of the decimal point represent decimals/ fractions of a day. This remains the case, and the value of the underlying Double variable remains constant, whatever format you may apply to DISPLAY the date or whatever regional settings apply.

So assigning DATE1 - DATE2 to a Double variable will give you the difference between them in days and decimals of a day.

Confusion arises when you place the mouse pointer over a Date variable in code because intellisense then gives you a formatted date in the TextTip popup based on regional settings for the computer you are using.

 

[newora]

Hi again,

Thanks yet again for the replies - I was just using the format function to try to highlight my point, which I probably did not do very well!! It just helped to highlight the fact that I was confused !!

I think I have got it now thanks guys - as hughlerwill said I think I was getting confused by looking at the values via intellisence within the IDE.

All of the variables that hold dates are definately defined as type date so I think I just need to ensure that I do not have any format funtions lying around anywhere!!

Thanks again All.

Newora

 

[newora]

Sorry guys, just one more query, which I think is the underlying cause of my confusion really !!

The routine that I have has to get a date (just a date) from a database table and then needs to get a change of shift time from another database table.

I thus need to get the date and time put together into a date variable - this was when I thought try using the format function!!

Thus how would I go about 'concatanating; a date column from 1 database table with a time (HH:MM as a string) from another database table and get this into a date variable so that I can compare it with another date variable (which would probably contain now())

Thanks yet again

Newora

 

[BobRodes]

Well, try this:

Dim dt As Date
dt = "1 / 1 / 2007"
debug.print dt
Debug.Print dt + 0.75

And see what you get.

HTH

Bob

 

[HughLerwill]

re my previous;
<<So assigning DATE1 - DATE2 to a Double variable will give you the difference between them in days and decimals of a day.>>

Do not be tempted to assign the result to another Date varaible.

Having assigned it to a double you should then manually format it in code for display.

Print "Time difference = " & int(MyDouble) & " Days " & 24 * (MyDouble - (Int(MyDouble)) & " Hours"

extending this logic will give you minutes and seconds too.

You can of course use the DateDiff function to find the number of Days, Hours, Minutes, seconds etc. but you will have to manually calculate/ format the displayed difference result.

 

[Hypetia]

>'concatanating; a date column from 1 database table with a time

Concatanating a date and time is easy. Just add the two variables cotaining date and time.
___
[tt]
Dim dtDate As Date, dtTime As Date, dtDateTime As Date

dtDate = Date
Debug.Print dtDate 'contains date only

dtTime = Time
Debug.Print dtTime 'contains time only

'adding date and time
dtDateTime = dtDate + dtTime

Debug.Print dtDateTime 'contains complete date time.

'comparing...
If dtDateTime > Now Then 'any kind of comparison
'...
End If[/tt]
___

Note that the statement in blue is not a simple string concatenation. Rather it is a numerical addition of two dates, although it looks like a simple string concatenation.

Bob, as I said above, never assign strings to date variables. The statement like[tt] dt = "1 / 1 / 2007" [/tt]is too dangerous. Because if day and month are not same, like "1 / 2 / 2007" then the interpretation of this date will depend upon the current regional setting and you will experience the same problem as defined by OP.

To prove the point just run the following single line code, once with English (US) and then with English (UK) settings.
[tt]
MsgBox FormatDateTime("1/2/2007", vbLongDate)
[/tt]
If you want to assign a fixed value like 1/2/2007 (Jan 2, 07) to a date variable, always use Date lieterals instead of string literals. Date literals are always interpreted in EN-US format (#MM/DD/YYYY#) and this interpretation does not change with regional settings.
[tt]
dtDate = #1/2/2007# 'right. Always Jan 2, 07
dtDate = "1/2/2007" 'wrong! Jan 2, 07 or Feb 1, 07??
[/tt]
If the month, day and year are variable then you cannot use date literal assignment. In that case use DateSerial function to construct a date instead of string concatenation. Following code segment will illustrate this point.
___
[tt]
Dim dtDate As Date, D As Long, M As Long, Y As Long
D = 12
M = 6
Y = 2007

dtDate = M & "/" & D & "/" & Y 'wrong! Jun 12, 07 with EN-US, Dec 6, 07 with EN-UK
dtDate = DateSerial(Y, M, D) 'right. Always Jun 12, 2007[/tt]
___

This is the most common problem people do when handling dates. See the following threads, for example.
thread222-864463
thread222-1070982

The bottom line is:
1) Never involve strings in date maths.
2) Never use Format function in date assignment. It should be used only for formatting text/display data.

 

[newora]

Thanks again guys for the assistance - especially Hypetia ( have a star).

Seems all nice and simple and logical now to be honest!!

Thansk again