mysql date issues

[babyjenniferLB]

why is the default date format 0000-00-00 and why cant i change it to 00-00-0000

[notnek]

i always just have a row varchar(10) and whenever im running some function i have date('m.d.Y'); inserted into that row. creates 06.16.2008

[Knox]

yyyy-mm-dd is the international standard date format, which has the advantage of not being ambiguous like 00-00-0000 (i.e. is it mm-dd-yyyy or dd-mm-yyyy?). Within MySQL statements you can use the str_to_date and date_format functions (http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html) to change other formats into the format MySQL wants and vice versa. Or, you can do the transformation within the programming language - strtotime() and date() in PHP for example.

The problem with storing dates as strings is that you can't (i think) use MySQL's built in functions for manipulating dates, and it won't be as efficient.

[babyjenniferLB]

thanks for the help, unfortunately i cant store as a varchar as it makes sorting them ascending and descending impossible so i will just have to leave em at the current format.

[Luveno]

You can always alter them to whichever format you'd like when you're pulling them back out of the database pretty easily, if the format is a hassle.

[nomade]

$date_format=explode('-',$line['date']);
echo "".$date_format[2]."-".$date_format[1]."-".$date_format[0]."";

[HomeBru Studios]

If you are a programmer, there is actually a good reason for it! It is known as Japanese format and if the date was in a text string, it would be immediately sort-able without having to juxtapose the date's components first.

why is the default date format 0000-00-00 and why cant i change it to 00-00-0000

[SrWebDeveloper]

Some informal thoughts, a bit of a sidebar discussion...

Traditionally I use the "datetime" type, which is 0000-00-00 00:00:00 and parse the time/date as I need it via code. You can also sort on this field and its useful to calendar driven apps or apps that use form fields to search by year, day, month, hour min and second (plugging in variables into a query). This method is also quite useful when browsing the inserted records via phpMyAdmin or some external utility like Toad, etc., because the datetime fields are human readable. Plus, MySQL has alot of date and time functions that work very well with this field without needing to do any additional conversion.

Or...

If you don't need to browse the database manually nor do you wish to use the advanced date/time functions built into Myself then one of the most efficient and SIMPLE methods for storing and sorting based date and time is to define a field as int(11) , name it timestamp, and store the Unix timestamp. Then you can use from_unixtime() and unix_timestamp() functions to convert to/from that format. And obviously sorting is efficient, even if you define the type as varchr(11). It still works.

The OP wants to store only the date, I follow that, but both these procedures are efficient, sort well, and happen to include the time which might be useful to the developer anyway for history logging.

Just thinkin' out loud, the previous advice was excellent.

-jim

[babyjenniferLB]

thanks for the replys folks got it the way i like, still confuseing why it is as it is but hey it looks like i wana i dont mind how sql sees it