Date strategy question

[Milani]

Background... My MySQL table originates from a web form email/csv generator. I import csv file on regular basis to MySQL to keep it up to date.

 

90% of my users will use a form that employs calendars to input data into a date field. So I can guarantee that I can get date data in whatever format I want.

 

I think the MySQL standard for a date field is (YYYY-MM-DD).

 

I was going to just record my dates as a string but if I am to future proof my system and take advantage of the benefits of a "true" date search (equal, before, after, date spread) – I should capture my field in a specific date format.

 

1. Is that right?

 

2. Does it matter which format I choose? (YYYY-MM-DD) is not very user friendly. In Australia it is (DD-MM-YY). I guess my script would have to take a (DD-MM-YY) date and convert it to (YYYY-MM-DD) before it can got into MySQL?

 

Now, the tricky bit. That other 10% of my users may use an alternate web form designed for mobile devices that does not use a javascript calendar. They might enter (Monday) or just 9/9 or 09/08. Anything.

 

3. What happens if you try and send a not-in-format string to a MySQL date field? Will it stick or will it just get rejected by the table?

 

4. Am I correct above when I say that capturing a date is better than capturing it as a string. Because a date field allows for more sophisticated date searching?

 

5. If I decide that keeping the date as a string is what I have to do. Are there workarounds that programmers can employ?

 

Any advice would be much appreciated. Thanks

 

[AndyB]

#1 and #2 - Store date in the database as a date using the standard (ISO date) format yyyy-mm-dd.  That will make any/all date comparisons or searches much simpler than if you use some cobbled-together pseudo-date or localised variant format.  Display your dates in a user-friendly fashion, i.e. Oz-style in your case.

 

Tricky bit: do all you can to avoid user-chosen formats for date.  Given them html dropdowns so they can ONLY select day#, month#,year# from canned selections.

 

#3 - disaster happens

 

#4 - you bet you're right.

 

#5 - why would you want to even think of doing that?

[Milani]

Really good of you to reply so quickly. Thanks for sending me on the correct path. Milan