What is the best method to keep dates working 5

[x508]

Hi.

What would be the best method of keeping dates working propperly.

My app stores alot of info to the DB and marks it with a date.

The operator for the app might sometimes enter a different date format than the one I specified (don't ask)...

What would be the best way to not miss anytihing in the DB when pulling reports.

i.e Searching for 2004/01/*
if a record holds */01/2004 it will not be included in the recordset. Someone, or some app, might also occasionally change the system date format and that messes up my whole thing again...

If I specify to use "yyyy/mm/dd" and the computer date format is "dd/mm/yyyy", it will save it as that, not so?

i.)Should I format the date that she enters immediately to the right format, and then save to DB?

ii.)Is there a way in which the SQL query can be "intelligent" and work with this kind of date problem.

I thank you in advance

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[JeffTullin]

You should validate the typed date in your app before you allow it into the db.
The db field should be a Date type, not a string.

Validate the date against entered.
Convert it to a date.
Store it in the db.

Then, you can search the db in a 'date format transparent' way.
You can display stored dates to match the user's system prefs, whatever they are.

 

[zemp]

I agree, store your date values as date types in your database. This will mean that you have to validate user entry for dates or force them to use a date picker (which returns a value of type date).

Take Care,

zemp

"If the grass looks greener... it's probably because there is more manure."

 

[shannanl]

I agree. Users will find a million different ways to enter a date or other data types for that matter if you do not force one way. I use maskedit boxes, date pickers, monthviews, or calendars to force then to enter dates the way I (database) wants it. Better to catch it on the front end.

 

[x508]

Thanks to all, I now have better ideas, and allrady have implemented some....

I appreciate

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)

 

[LPlates]

You could use combo boxes so they select a day of the month, another for the month and finally the year so you eliminate user input

 

[LPlates]

That applies for any kind of input be it date or whatever
but the DTPicker control would work nicely too as already suggested

 

[manitoba]

Whenever possible, use ISO8601 date format. It looks like this: YYYY-MM-DDTHH:MM:SS:mmm This is not only a standard format but is great when you need to sort dates because you can use a text sort algorithm.

 

[chiph]

The important thing to remember is to store the date as a native database type, and write code to translate the mangled values the user gives you into that format. That way if you run any reports off the database the report engine only has to deal with the native type, and not a zillion different ways of representing February 29th.

FYI-
The ISO-8601 format is also codified as internet standard RFC-3339 (

http://www.faqs.org/rfcs/rfc3339.html),

and is the official date format for Japan.

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[strongm]

ISO8601? argghh! See thread222-646646 for our last little discussion on that...

Mind you, just using it to be completely unambiguous about dates is exactly where it works well. Rather than using it to demonstrate that Microsoft's, VB's and everbody elses idea of what a date was was incorrect (which seemed to be where the referenced thread was going)

 

[chiph]

I missed that other thread somehow (and I'm a little glad I did!). It seems like the poster had an idea fixed in his head how weeks are allocated to years, and couldn't conceive that someone might do it differently. The response that the DatePart function works as documented was probably the best one.

In the case of this thread, I think that x508 was getting the presentation of the date confused with it's storage. I think both of us will agree that it's best to store a date in your database's native date format, as other tools can then deal with it easier (reporting, analysis, etc). And I think that both of us will agree that users tend to enter dates in a mish-mash of styles. The trick is either to get them to enter a date consistently (using picker controls, drop-down lists, etc), or writing an algorithm to interpret what they've entered (not an easy thing to do!).

Chip H.


If you want to get the best response to a question, please check out FAQ222-2244 first

 

[strongm]

Yep

 

[x508]

Tnanks

**********************************
May the Code Be With You...
----------
x50-8 (X Fifty Eigt)