SitePoint Sponsor |
|
User Tag List
Results 1 to 18 of 18
Thread: Date?
-
May 30, 2004, 14:38 #1
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Date?
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.
-
May 30, 2004, 14:47 #2
- Join Date
- Dec 2003
- Location
- Coral Springs, FL
- Posts
- 9
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
in the MySQL querey just use CURDATE() and that will insert the current date
-
May 30, 2004, 15:06 #3
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK, thanks. What format though?
-
May 30, 2004, 15:20 #4
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?Last edited by Josh_; May 30, 2004 at 16:09.
-
May 30, 2004, 18:02 #5
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 30, 2004, 18:33 #6
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 30, 2004, 18:38 #7
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no, use DATE_FORMAT in the query, it's a lot easier, and makes for cleaner code too
-
May 31, 2004, 07:43 #8
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm affraid I do not see date_format anywhere in phpmyadmin...Is it a function?
-
May 31, 2004, 08:10 #9
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
it is a mysql function
13.5 Date and Time Functions
-
May 31, 2004, 08:35 #10
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Hmm...I still don't see it. Do I need a certain type specified for that column before I can see it?
-
May 31, 2004, 08:40 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
could you please show your query?
Code:select '2004-05-31' , date_format('2004-05-31' , "%W, %M %d, %Y") 2004-05-31 Monday, May 31, 2004
-
May 31, 2004, 08:43 #12
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
I'm assuming you mean my table create SQL? If so, where do I find that...sorry, I'm new to this.
-
May 31, 2004, 08:45 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no, i meant the query where you select the formatted date out of the database
-
May 31, 2004, 08:47 #14
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 31, 2004, 08:56 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 31, 2004, 08:58 #16
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 31, 2004, 09:06 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 31, 2004, 09:09 #18
- Join Date
- Apr 2004
- Location
- NC
- Posts
- 689
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
OK, gotcha. Thanks alot!
Bookmarks