Date?

[Josh_]

OK, well I'm not sure what to do. In my table i have a column for date. I would like the date I insert to be in mm/dd/yyyy format. And I need to be able to sort records by date...oldest, newest, etc. How should I go about this? Should I use a special format in mysql? I know in php I can just use the date function and go from there. But I'm not sure if I should just set the type of the date column as varchar, or if I should use some special date format for it.

Thanks for any help.

[PCGenious]

in the MySQL querey just use CURDATE() and that will insert the current date

[Josh_]

OK, thanks. What format though?

[Josh_]

Well I just tried it, and it inserted it like this

2004-05-30 00:00:00

Is there a way to format it? And would it matter if it was varchar or not when I try to sort it?

Edit:


Well i've just found out about the timestamp type.
http://dev.mysql.com/doc/mysql/en/TI...P_pre-4.1.html

I guess that could work well, with a timestamp 8. And then in my php i could somehow figure out how to edit it so it displays mm/dd/yyyy. But I'm not sure how since there's nothing to explode it with...

Any comments?

[r937]

mysql, like all other databases, has a special internal format for storing date values

(in some databases, it is an integer number representing the number of days since a base date like dec 31 1900 or something)

so what you have to learn is the special syntax to get dates into the database (so that the database will recognize the specific value you intended) as well as the special syntax to get dates out of the database (so that you can display it the way you want)

in mysql, you must enter dates in year-month-day sequence, but you can say '2004-05-30' (string) or 20040530 (number) or quite a few other variations

the good news is, getting it out is drop dead simple

just use either the DATE_FORMAT or TIME_FORMAT function

so "I would like the date I insert to be in mm/dd/yyyy format" is a tiny bit off -- you cannot insert it that way, and it certainly isn't stored that way, but you can display it that way, or any other way that you wish

[Josh_]

Thanks

I got some help from somebody doing something similar that I want to be doing, and he said to use the NOW() function. I tried that, and the format is pretty much what I need. I can just use some string functions in php and manipulate it like I want.

[r937]

no, use DATE_FORMAT in the query, it's a lot easier, and makes for cleaner code too

[Josh_]

I'm affraid I do not see date_format anywhere in phpmyadmin...Is it a function?

[r937]

it is a mysql function

13.5 Date and Time Functions

[Josh_]

Hmm...I still don't see it. Do I need a certain type specified for that column before I can see it?

[r937]

could you please show your query?

select '2004-05-31'
 , date_format('2004-05-31'
      , "%W, %M %d, %Y")
      
2004-05-31     Monday, May 31, 2004

[Josh_]

I'm assuming you mean my table create SQL? If so, where do I find that...sorry, I'm new to this.

[r937]

no, i meant the query where you select the formatted date out of the database

[Josh_]

Well the thing is, I don't have a query. Right now I'm inserting stuff, but I don't know what format I should have the date column in. I want to eventually be able to select it and arrange it by date...but right now I want to get stuff inserted correctly.

[r937]

okay, i understand

as mentioned in post #5, you must enter dates in year-month-day sequence

that gets the values in

as far as the datatype is concerned, just declare it DATE

you can later display it in any format you want

[Josh_]

Ah, I see!

So, I should keep the type of that field as varchar, and just enter it like this?
2004-05-30

and then when I need to select it, I use the date_format function?

Thanks for the help!

Edit:


I just saw your edit, do I need to set any length for the date format?

Edit:


well I just tried it, and it enters it yyyy-mm-dd like you said it should. Should I set that column as NULL by default though?

[r937]

yes, use NULL for the default

use DATETIME instead of DATE if you want each value to include a specific time component

otherwise, don't

[Josh_]

OK, gotcha. Thanks alot!