Hi Chris, John all!
Chris' explanation is nearly correct:
Chris Sansom wrote:
At 15:43 +0100 23/7/06, John Berman wrote:
I have a table called: submissions and each record has an approvedate
field
which stores the date mm/dd/yyyy
Why? If you're storing the date in this format [[...]]
John,
you should ensure to differ between storage format and presentation
format(s):
- For storage, ensure that it can be sorted on (order year, month, day).
Typically, this is achieved by telling the database a proper type
(like DATE or DATETIME) and then just supplying a value.
- For presentation, use what suits the users' taste.
This may be configurable or depending on context (like drop the year
if it is obvious etc.), and you can achieve all (?) desired results by
the DATE_FORMAT() function.
There should be only one storage format, but there may be many different
ways to present a date.
If you do not use the proper SQL type (DATE, DATETIME, ...), you cannot
rely on SQL doing the desired operations (sorting, calculations, ...).
[[...]]
The SQL format - in decreasing order of unit size - is of course the
most logical way because you can guarantee to sort on it and do other
calculations. [[...]]
That is correct except for the designation "SQL": It really is an ISO
format.
The important property is having the most significant value (year) in
the first (leftmost) place.
Joerg Bruehe
--
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
