turning a MySql date format in to normal format

@Markbad311Feb 11.2006

very simply how do I go about taking a date out of a DATE field in the DB and make it 1-29-2006 format and also how can I get a format like:
Friday 2-10-2006 slashes or dashes doesn’t matter just anything is less confusing to normal reader then the MySql format 2006-01-29.

Thanks in advance!

to post a comment

PHP

14 Comments(s) _↴

@bathurst_guyFeb 11.2006 — #Please read [url=http://www.webdeveloper.com/forum/showthread.php?t=94789]this thread[/url] and see if that answers your problem.

@Markbad311authorFeb 11.2006 — #very useful thread. thank you. I do however have a follow up question. I am using a statement like

[code=php] 
 $sql = "SELECT * FROM $table_name WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) and DATE_ADD(CURDATE(), INTERVAL 1 DAY) ORDER BY 'bar'"; 
 [/code]

so a SQL statement may be out of the question right? Should I use date convert by Chris McKee?

@Markbad311authorFeb 11.2006 — #and how about expanded date format?

@bathurst_guyFeb 11.2006 — #Yes you might have to, if the php date() function doesnt work for your situation?

@Markbad311authorFeb 11.2006 — #yeah the date function won't help I just need the date out of the DATE field in the DB ok thanks a lot. I will get working on it. anyone who wants to take a stab at the other thread I got rolling here it is

[URL=http://www.webdeveloper.com/forum/showthread.php?t=94755]If Else Nightmare[/URL]

@chazzyFeb 11.2006 — #very useful thread. thank you. I do however have a follow up question. I am using a statement like

[code=php] 
 $sql = "SELECT * FROM $table_name WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) and DATE_ADD(CURDATE(), INTERVAL 1 DAY) ORDER BY 'bar'"; 
 [/code]

so a SQL statement may be out of the question right? Should I use date convert by Chris McKee?[/QUOTE]

Why would a sql date function be out of the question? In reality, you shouldn't use * in your SQL statements anyways. It's ambiguous. Yeah sure, I know what you'll say now "But chazzy I've seen you write it lots" I know, its me being lazy. You should select the data that you need and only the data you need from the table.

Unless, of course, I missed the point.

@Markbad311authorFeb 11.2006 — #well I need all the data out of the table it is just one of the fields contain a date that I would like to change in to a regular format.

@chazzyFeb 11.2006 — #so just do this:

<i>
 </i>SELECT
 col1,
 col2,
 col3,
 ....
 DATE_FORMAT(<span><code>date_col</code></span>,'%M-%d-%Y') date_col
 FROM
 <span><code>your_table</code></span>
 WHERE
 <span><code>date</code></span> BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) and DATE_ADD(CURDATE(), INTERVAL 1 DAY) ORDER BY 'bar'

and then just properly enclose it for use in PHP.

@NogDogFeb 11.2006 — #

[code=php]
 $sql = <<<EOD
 SELECT *, date_format(date, '%c-%e-%Y') AS date_mdy
 FROM $table_name
 WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND
 DATE_ADD(CURDATE(), INTERVAL 1 DAY) ORDER BY bar
 EOD;
 
 [/code]

@Markbad311authorFeb 11.2006 — #when I use EOD no quotes then? I like both methods just for my info type question

@NedalsFeb 11.2006 — #In reality, you shouldn't use * in your SQL statements anyways.[/QUOTE]
In follow up to chazzy's comment.

You should never use '*'.

If you should ever alter your SQL table, which happens more often that not, you will have to alter your script, or many scripts, as well. If you define each column then your scripts can remain unchanged.

@chazzyFeb 11.2006 — #

[code=php]
 $sql = <<<EOD
 SELECT *, date_format(date, '%c-%e-%Y') AS date_mdy
 FROM $table_name
 WHERE date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND
 DATE_ADD(CURDATE(), INTERVAL 1 DAY) ORDER BY bar
 EOD;
 
 [/code]

[/QUOTE]

this is the right syntax (for php) you'd need for this, but improper usage (sql). you're doubling some of your data, for one thing. "SELECT * FROM TABLE" really means "get all columns from this table that match *". You know that you can be more specific than that, and you should follow that. Also see Nedals' comment. Yes, this will work, but in some cases you should take this into consideration.

@NogDogFeb 11.2006 — #In follow up to chazzy's comment.

You should never use '*'.

If you should ever alter your SQL table, which happens more often that not, you will have to alter your script, or many scripts, as well. If you define each column then your scripts can remain unchanged.[/QUOTE]
I understand what you're saying, but practically speaking, if you use mysql_fetch_assoc() to retrieve the result row data, it really won't matter if you add columns and/or rearrange the existing columns. The only disadvantage (well, other than the fact that you're running less than optimal code, so you might be adding a couple microseconds and a few bytes of memory per row - which could be important in some circumstances, mind you) would be if you deleted a column that you need, and thus no error would be generated by the query and would only show up elsewhere in the code.

For small, non-mission-critical applications, I'm not going to lose any sleep over the use of "SELECT *".

@NedalsFeb 11.2006 — #... if you use mysql_fetch_assoc() to retrieve the result row data....

For small, non-mission-critical applications, I'm not going to lose any sleep over the use of "SELECT *".[/QUOTE]
I'm not familiar with PHP's database calls. (I use Perl), but I believe mysql_fetch_assoc() is equivalent to perl's fetchrow_hashref() which is probably the least efficient calling method. My comment really addresses programming style.

And I agree with you latter comment. ?

Also in #PHP _↴

Array() help needed!Php session won't resume data How to add links for disabled people to increase fonts?

Success!

Help @Markbad311 spread the word by sharing this article on Twitter...

Tweet This

turning a MySql date format in to normal format

14 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

turning a MySql date format in to normal format

14 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

14 Comments(s) _↴

Also in #PHP _↴