I'm beginning to program a new application that will use Date at great length. Since I see many questions concerning date, I'm wandering what should be the best practice to save the Date value in the database. I'm reluctant to use Date/Time field type since this would mean a lot of conversion and not all RDBMS stored the Date/Time in the same way.

I've heard from a friend that he stored the Date in a Char field in this format: YYYYMMDD. With this format, he doesn't have to worry how the date formatted on the user computer when making is manipulation of the data. He also create some standard function for converting the date for display and back. If is program doesn't support a specific formatting, he just have to modify the standard function and all the program is modify with little effort.

So what technique should I use or do you know a better way to do this?

Stéphane Lajoie

__________________
Stéphane

A programmer is a device that transform coffee in code lines

\"Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.\" Rich Cook

I see nobody else is willing so far to weigh in on this one. I'll give it a shot. I'd be interested in others' opinions, since I don't think this is a completely clear-cut issue.

For what's it's worth, and it's only my opinion, I don't think it's a good idea to store a date as a character string. I don't think it's a good idea to store numeric data as a character string either. I guess this really speaks to the notion of datatyping. Why do we have datatypes at all? Everything could be stored as a string of characters, so why don't we? Wouldn't that be "simpler"?

If you've ever programmed in a typeless language (e.g. vbscript, where everything is a variant - there are other languages) and had to track down a bug because the wrong type of data ended up where it shouldn't, then you know why. It's too easy to end up with a value like '20031332' which might lie dormant in a row somewhere and bite you long after the damage is done and the trail to the offending process that created this garbage is cold. You simply cannot get such trash into a column defined with one of the datetime datatypes, thus forcing you to write much more robust systems.

Coding dates as a character string means you cannot directly utilize any of the date manipulation functions presumably provided by the RDBMS (e.g. SQL Server's dateadd, datediff, datepart, day, month, year, etc. functions). You'd either have to convert the character string to an intermediate datetime type to invoke the function, then convert it back to use it anywhere, or resort to ugly and slow character manipulation to implement the equivalent functionality.
I don't understand how storing data in its native datatype would "...mean a lot of conversion...". To move data from one RDBMS to another would mean some sort of conversion no matter what datatypes are involved. You'd be more likely to be able to take advantage of any conversion tools provided by the vendor if you use "standard" datatypes.

Concern about client system formatting I think is a red herring; this is a presentation issue on the client, and should be dealt with at the client. The RDBMS is responsible for presenting data to the client in response to client requests. Formatting that data is a client responsibility. Any client which provides worthwhile tools for accessing an RDBMS will provide mechanisms for dealing with the datatypes that RDBMS presents.

You see many questions regarding dates because people either don't understand how a datetime datatype "works" (i.e. it stores both a date and a time), or because they confuse data retrieval with reporting. If all you really care about is dates and have no interest in the time, it's a simple manner with a little care, to arrange for the time to be always be set to midnight everywhere, thus effectively ignoring the time altogether.

Just one man's opinion :)





Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

I am glad that Jeff opted to reply before me, I was worried I would make an embarrassing mistake, like confusing my ORDER BY and GROUP BY clauses (see the thread http://p2p.wrox.com/topic.asp?TOPIC_ID=2851 if you don't get the reference).

I totally agree with Jeff. If the RDBMS has a Date/Time data type then use it, that is what it is there for. I have had countless problems with dates in my early programming life, because I always used to format dates in UK format (dd/mm/ccyy) as that is what I was used to. Now that the software has gone international there were lots of little errors that cropped up with this method. I later learned about the ISO date format (ccyy-mm-dd), using this format there is no possibility of the date being formatted incorrectly.

These points are totally related to the client side and not the RDBMS, which I think is what Stéphane is concerned about. As long as your software inputs dates in a format that is impossible to confuse (no matter what the local date format is) and the field in the database is a Date/Time field, and then when you retrieve the date from the database you format it into what ever format you want then there should be no problems with this approach at all.

I hope I am making some sort of sense.

Regards
Owain Williams

Thank you for your opinion Jeff and Owain.

Like you said Owain, my biggest concern is the format of the date. I know for sure that my application will be use by people who have the US format (MM/DD/YYYY) and the French Canadian format (DD/MM/YYYY). So as long as I remember to force the client to enter the date in the ISO format, I shouldn't have any problem.

I ask this question because I had a bad experience with the Canadian format number. The user using that format keep crashing for no apparent reason. After 3 days of testing I found out that VB return the number with a comma for the decimal separator. So when I was constructing my SQL query, it end up with 2,5 for the number. It took me a week just to correct the entire program, so I didn't want to repeat that an other time.

Stéphane Lajoie

When the user enters the date, they will probably enter it in their own native format. If their computer is set to that format then you will be able to IsDate() the value entered. Assuming this test returns True, you can then convert it into a date and format it into ISO format thus:

Once you have done this you can include the value in your SQL statement without the possibility of the query analyser becoming confused about the date. You could also do the same with 3 combo boxes, one for the day, one for the month and one for the year:

    This is approach I prefer because the user can't type in the date incorrectly, they must select the values from combo boxes.

Regards
Owain Williams

Thanks again Owain. Well now it's time to decide how the user will enter the dates. One think for sure, I'm now convince to use the Date/Time type.

Stéphane Lajoie