date

[slighltywhacked]

Hey all...

Im working on a news database/ aplication. When a user fills in a form with the approiate details like the headline and article text, the date that the article was entered into the database has to be recorded into the database as well.

I then want to have the ability to pull the date out and have in in the format of

March 12, 2001
MONTH DD, YYYY

And one last brainteaser, the dates have have the ability too be sorted by date. So the newer articles come before the older ones

[freddydoesphp]

You can use the built in MySQL function DATE_FORMAT(field, mixed_format);

$sql = "SELECT DATE_FORMAT(datefield, '%M %e, %Y') as newdate from tablename"; 

[slighltywhacked]

Does it have to be stored a specific way on the database then??

How can I test what date is newer If I do it that way

[freddydoesphp]

You can find out the format that the date must be stored in here:
http://mysql.he.net/documentation/my....html#DATETIME




And look at DATE_SUB() for checking if the date is newer than a specific date
http://mysql.he.net/documentation/my...time_functions


A word of caution, you must store the date in a date or datetime type field, in order to use any of the built-in MySQL date functions.

[oodie]

You can also sort them while selecting

$sql = "SELECT DATE_FORMAT(datefield, '%M %e, %Y') as newdate FROM tablename ORDER BY datefield"; 

[smorb]

I have a further question to this problem. I get the date stored in a db ( as unix-timestamp ) like you said above:

$sql = "SELECT DATE_FORMAT(datefield, '%w,%M %e, %Y') as newdate from tablename"; 

how can I change the information of the "w" (day of the week by number sunday=0 ...) to the german names of weekdays. I need something like this here:

<?switch (datefield("w")) {
case 0:echo "Sonntag";
case 1:echo "Montag";
case 2:echo "Dienstag";
case 3:echo "Mittwoch";
case 4:echo "Donnerstag";
case 5:echo "Freitag";
case 6:echo "Samstag";
}

but I don't know exactly how to combine it here?
Thank you for helping, smorb

[oodie]

What I'm thinking is to set up another table to store the weekdays in Germany like this

Table day_name :
---------------
id | name
---------------
1  | Sonntag
2  | Montag
3  | Dienstag
4  | Mittwoch
5  | Donnerstag
6  | Freitag
7  | Samstag
---------------

And the query would be like this:

$sql = "SELECT CONCAT(day_name.name,\", \",DATE_FORMAT(datefield,'%M %e %Y')) AS newdate FROM tablename,day_name WHERE day_name.id = DAYOFWEEK(datefield)";