No problem, curious about date procedures...

[StormbringerX]

Hey all,

I'm using VB6 and sql2000, and I find that dates seem to give me more problems, more consistently, than anything else. Currently I'm leaning toward the datepicker control but as everyone knows, it definitely has its drawbacks. I'm wondering if there are any rules of thumb that people out there use concerning dates. Meaning, do you use a certain type of control when a date is not required in the field, do you use a certain type when you need to do extractions (such as pull only the year, or if you need to detemine the days between two days). Do you use any special steps according to the database that you're working with?

The application I'm currently working with uses dates in sooo many different ways that I'm curious how other professionals deal with the inconsistencies (or maybe I should say: the multiple ways of handling with) dates.

Thanks gang...

Dave

 

[ufobaby]

hi,

if am not wrong u can look at using "format" function which allows u to format date in many different ways.

also i don't think any kind of a control should matter. finally if u store it in a variable u can deal with it the way u wish to.

hope this help u

__________

Niraj...
"The unexpected success is not just an opportunity for innovation. It demands innovation."

 

[chiph]

There's four main problems people encounter with dates: Displaying the date value for the user to see and edit; Storing the date value in your database; Timezone issues; and what happens when you don't have a date.

The first is constrained by the fact that the user can set their date format to whatever they like in the control panel. Unfortunately, sometimes that often causes problems (is 1/6/03 the 6th of January, or June 1st?). To avoid these issues, use a mask control, or a date-time picker control. Do not use a regular text box.

The second problem comes when you have to enter this date into your database. SQL Server is picky about it's date formats, and the way to avoid any problems is to make sure your date is formatted in ISO-8601 -- yyyy-mm-ddThh:nn:ss (the "T" is a literal).

The timezone problems can be avoided by always storing dates as GMT/UTC. When you go to display the date, you add the current user's timezone offset (available with an API or Javascript call). When writing the date/time, you reverse this and subtract their timezone offset. This way each user sees dates relative to their location. However, this can sometimes cause confusion when two users in different timezones are talking on the phone to each other - the solution there would be to have the app only display UTC (make the user do the math), or educate the user about how the app functions.

Lastly, dealing with the "no date" problem. It would be best if you had a control that allows the user to skip entering a date (such as leaving the date of delivery empty when entering a customer's order). The alternative would be to use a "special" value that indicates when the date is to be ignored (SQL Server uses 1899-12-31T00:00:00 for this purpose). When you see this value, you would write a NULL to that column in the database (NULLs indicate a lack of data in database-speak). When reading, if the column is NULL, you'd put the special value back. This is obviously a patch - but it might be needed.

Hope this helps.

Chip H.

 

[Cresby]

if you are talking different countries you have to consider what people expect the format to be as they enter it manually (say) is it yy/mm/dd (syntactically the most logical when followed by date) or dd/mm/yy or mm/dd/yy which I can never fully come to terms with. Where dates are unambiguous VB copes well which actually fools you for a time, well it did me. AND DateSerial gives a wrong answer to Feb 29 in non-leap years but no error condition. (SP3 anyway) OOPS.

 

[avanderlaan]

I keep date/time information in "UNIX" time as a long integer in the SQL Server and IBM DB2 databases. I then format these using the Windows API and user locale information.

 

[Cresby]

Personally I find putting the date into English and back on the screen as a user confirmation is a good wheeze where manual input is concerned. eg "February 3rd 2004" but if you are talking many countries this becomes a logistical nightmare or a PR problem. How about a big box with TODAY's DATE is:blah blah blah so that the user has at least one clue as to how you think the date is going to be handled. Seperate boxes for yy mm dd sort of spoon-feeds them. If it is an internal thing you are probably stuck with a variety of formats and maybe the only way is to add a code byte somehow to identify the source &/or format of the data, as long as the system can ingore the extra bytes until the code is modifed to use it.

 

[Cresby]

Just a wild thought but if you don't need time and it is included in the format ................ encode a time for each format - say fractions of a minute wouldn't hurt anyone - normally. You won't have 60 formats even with countries taken into account - surely.

 

[StormbringerX]

It seems the "no-date" issue is the bear in my forest.

Whenever a date is required, I use the datepicker. I have fewer problems with validation.

But for those instances when the date is not required, but any date entered must be validated, It seems I'm always hitting an 'invalid use of null' or some other bothersome message. So I'm trying to come up with a standard way of handling dates depending on the requirements of the user, the app, and the database.

I certainly appreciate everyone's input. Thanks so much

Dave

 

[chiph]

One of the things I've seen done is you place a checkbox next to the date that enables/disables the date picker. A little cheesy, but it has the benefit of being cheap.

When it's disabled, you write a NULL to the database. When it's enabled, you write the value from the picker. When loading the form, if you have a NULL from the result set in that column, you clear the checkbox which will (hopefully!) disable the picker. Otherwise you check the checkbox and load the value into the picker.

Chip H.

 

[mattKnight]

>One of the things I've seen done is you place a checkbox next to the date that enables/disables the date picker. A little cheesy, but it has the benefit of being cheap

I thought that was the point of the checkbox property of the DTPicker. Setting the DTPicker.checkbox property to true includes checkbox in the DTpicker. The DT picker will return NLL if this box isn't checked, or the selected date other wise

Take Care

Matt
If at first you don't succeed, skydiving is not for you.

 

[chiph]

OK, I hadn't seen that property -- certainly makes things easier. Thanks.

Chip H.

 

[DonPeters]

Matt/Chip

I too typically use the DTPicker with checkbox. However, when it is 'disabled' using the checkbox, the default date is still in there (ie if I create a dtpicker today, today's date is still there). Am I missing something or is there a way to make it blank when disabled?

Thanks,
Don

 

[gregoriw]

I've got a similar type of situation with working with a variety of pseudo-date string expressions that I must evaluate/compare as a true date. This must be done when my VB program has to check for duplicated dates.

Here are some varieties of the input date expression (most represent Month and Year only):
10/00/95
1/00
3/90
January 1998
4/94
1995
12-1996
Dec 1996
12/99
7/2002
01/00/01
08/00/99
8/00
Spring 1997

First thought I had was to use the VB Format function (like this strNewDate = Format("06/02", "mm/dd/yyyy&quot ) but it doesn't work each time with this because instead of returning the expected "06/01/2002" it returns "06/02/2003".

The alternate expression Format("June 1997", "mm/dd/yyyy&quot does appear to work and returns "06/01/1997" so it knows how to replace the text month to a number as in 06. Kewl.

Does anyone know of a routine to handle ALL date expression possibilities (as I've shown above) so that it returns a consistent date formatted string of "mm/dd/yyyy"?

Thanks for all your help,
gregoriw

 

[StormbringerX]

I don't know of any way to make the date in the datepicker blank, BUT at this link

http://www.mvps.org/ccrp/

They have a free datepicker control that DOES have a property that allows for blank dates. They also have several other useful controls. Unfortunately they're not updating the site anymore.....

Dave

 

[chiph]

gregoriw -

The idea is to prevent users from entering dates however they feel like. Once it's happened, its too late and you end up with dates like you showed us. I'm afraid you'll have to write some nasty code to parse them out. :-(

You'll have to decide on some way to handle the 1/6 -- Jan 6th or Jun 1st problem -- without additional information there's no way to tell them apart. I would throw them into a "reject" file for a human to look at and make a decision.

Chip H.