Fog Creek Software
Discussion Board




Passing VB Date Types

We have a rather strange problem with some code.

We have a client and a server.  The two machine probably have different locales and date formats.

The client is passing a date to the server as a paramater, and the month and year keep getting flipped.

The dataobject was created on the client using an RDS Datasace.

Has anybody ever experienced problems like this?  Are there any known bugs regarding the date type when passed over RDS?

I've tried google, but no luck.

Ged Byrne
Wednesday, May 14, 2003

What format are you using?
What locales are the machines running?
What database are you using?
What is the granularity of the date - ie does it include the time?
Do you have any control over the source code for the client and/or server?

Just to put your mind at rest, almost everybody has trouble with date formats at some point.

A good "common" format is, e.g. 20030514 10:38:00 - I don't know if it has a name.

General advice: Always have a DisplayDateFormat() function. You may also need a StoreDateFormat() function. Try and ensure that a common format is used when doing anything other than displaying dates. Doesn't really matter what it is, as long as its consistent.

Justin
Wednesday, May 14, 2003

The server has US locale and the client has either UK or any european locale.


The date is stored in a table on SQL server.  They are saved as strings in uk dmy format.

The date is being read correctly and converted using CDate() to a date.

There are 3 such dates held in a variant array that then get passed, via RDS, to a server object.

The thing I don't understand is that the date is being passed as a date, not a string.

Ged Byrne
Wednesday, May 14, 2003

Is there a particular reason you're storing the data as a string, and not ummm... I don't know..... a datetime?

Rick (www25.brinkster.com/rchildress)
Wednesday, May 14, 2003

> A good "common" format is, e.g. 20030514 10:38:00 - I
> don't know if it has a name.

It does, it's the International Organisation for Standardization ISO 8601. I wish it was more widely applied; most of the date/time problems I come across software projects derive from month and day ordering.

Using the standard removes this ambiguity, and standardises on the 24hr time format.

Pietro
Wednesday, May 14, 2003

I store all dates as 37551.1492824074 and format as needed... DateTimeFormat().

I'm sure this is repleat with problems but I haven't run across any yet.


Wednesday, May 14, 2003

We're using VBs internal date type.

I'm guessing it stores the date as number of days since 1900 or some other date.

Ged Byrne
Wednesday, May 14, 2003

Ged,

maybe this can help you?

http://yvolksoft.narod.ru/VBTips/Format4SQL.html

Just me (Sir to you)
Wednesday, May 14, 2003

clng(int(Now()))

Depending on what you are trying to do, it may be in your interest to convert the date to a long for storage and transit and only use date formats for display.

(Int() is needed so that dates after noon don't round up to tomorrow).

Ran Whittle
Wednesday, May 14, 2003

We've solved the problem by passing the date as a formatted string.

This is counter sense, surely a string shouldn't be preferrable to a specific data type.  Still, it works now.

Thanks for the help.

Ged Byrne
Wednesday, May 14, 2003

Ged:

I don't know if this is relevant or timeous, but I'd strongly recommend that when dealing with timestamp data from multiple timezones you ensure that they are converted to UTC in the client. Universal Coordinated Time is, to all intents and most purposes, the same as GMT. In the past I've come across all sorts of weird issues relating to sequencing of events when the clocks change in spring and autumn.

David Roper
Wednesday, May 14, 2003

>We're using VBs internal date type.

>I'm guessing it stores the date as number of days since 1900 or some other date.

I believe this is correct.  It's stored as a double, so that 6am is xxx.25, and 6pm is xxx.75.  This leads to the insane conclusion that time ran backwards before 1/1/1900: -1.25 is (temporally) before -1.75, but -1.25 > -1.75, which means that you can't do a simple compare to reliably find which of two dates is earlier.  This fact alone should be enough to convince you not to use them unless absolutely necessary.

Brian
Thursday, May 15, 2003

Brian,

>> This fact alone should be enough to convince you not to use them unless absolutely necessary.

Another option is to use the function that VB provides for date comparison - DateDiff.

As you imply, < > = should never be used with Dates in VB. But that doesn't mean you shouldn't use Dates - instead, just make sure you use DateDiff ("s", , ) when comparing them...

Seeya

Matthew
Thursday, May 15, 2003

A subject near and dear to my heart.  I suspect that I have the dubious distinction of knowing more about the bugs in the VB date handling code than any other human.

You guys are almost right:  the zero day is actually 30 December 1899, so day one is 31 December 1899.  And yes, for negative days the days run backwards but the hours run forwards, so to speak. 

Why, I hear you ask, is it the 30th? 

Good question.  There is a very silly reason.

Some of you may remember a product called Lotus 1-2-3 -- it was for a while the most popular spreadsheet application in the world until Excel unseated it.  123 stored dates in the following format:

* day one was 1/1/1900.
* no days before 1/1/1900 were legal
* days were stored as a double with the fractional part being the fraction of that day gone by.

For compatibility reasons, the excel devs decided to use this format as well, despite its obvious flaws.  They extended it to negative dates in this kind of weird way.  But then they noticed something very odd indeed.  The Lotus devs had forgotton that 1900 was not a leap year. 

The Excel devs then had a terrible choice.  They could

(a) abandon this ridiculous date format altogether, come up with a better way to store dates, and write a translator routine to go to/from the 123 format.

(b) replicate the bug

(c) move "day one" back one day, and be incompatible with Lotus for two months but compatible everywhere else.

The right answer was (a), but unfortunately, they chose (c), and we've been stuck with this silly date format ever since.  OLE Automation was written to be compatible with Office, VB was written to use the OLE Aut type system, and there you have it.

Eric

Eric Lippert
Friday, May 16, 2003

*  Recent Topics

*  Fog Creek Home