Sure is... SELECT DATE_FORMAT(dateField,'%Y%m%d') as dateField
Take a look at: http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html -Josh --- Yesmin Patwary <[EMAIL PROTECTED]> wrote: > Dear All, > > First of all, I would like to thank to Josh and > Peter Brawley for their kind help on previous issue. > > > Here is another Issue: > There are many tables that have timestamp field > with 8 char (yyyymmdd). I use this format all over > our website and to create various reports. Below is > static php code that I used numerous places: > <? > $year=substr("$date", 0, 4); > $month=substr("$date", 4, 2); > $day=substr("$date", 6, 2); > ?> > > All timestamp dependent reports and web pages had > problems, once I upgrade to MySQL 4.1.12. At the > end I had to downgrade to 3.23 to bring web site > operation to normal. Is there anyway to preserve old > timestamp format after upgrading? > > MySQL - 3.23 > +--------+------------+---------- > | Field | Type | Default (yyyymmdd) > +--------+------------+---------- > |log_date|timestamp(8)| 00000000 > > MySQL - 4.1.12 > +--------+-----------+-------------------- > | Field | Type | Default > +--------+-----------+-------------------- > |log_date| timestamp |0000-00-00 00:00:00 > > > Josh <[EMAIL PROTECTED]> wrote: > You could rotate the output... basically get 1 row > with 11 columns > (CA01_count,CA02_count,...,CA12_count) > (leaving out CA10) joining all 12 tables together... > > Or... perhaps we can help with the timestamp issues > you are having and get you upgraded to later version > of mysql that supports nested SELECT statements. > > --- Yesmin Patwary wrote: > > > Dear All, > > > > I had some issues in past with timestamp fields as > a > > result I am unable to upgrade to mysql 4.1 > version. > > I am sure below the query recommended by Josh > works > > with 4.1 or above. Would it be possible to rewrite > > this query for 3.23 version? > > > > Again, thank you Josh and all others for your kind > > help and comments. > > > > Josh wrote: > > Here's one method: > > > > SELECT cl1.list_name, count(*) as count > > FROM customerList cl1 > > WHERE cl1.id IN (SELECT cl2.id FROM customerList > cl2 > > WHERE cl2.list_name='CA10') > > and cl1.list_name != 'CA10' > > GROUP BY cl1.list_name > > > > --- Yesmin Patwary wrote: > > > > > Good morning all, > > > > > > We have 12 customer lists: CA01, CA02, .,CA12. > > > > > > Table: customerList > > > +-----------+------+ > > > | list_name | id | > > > +-----------+------+ > > > | CA10 | 20BE | > > > | CA07 | 20BE | > > > | CA11 | 20BE | > > > | CA03 | 20BE | > > > | CA10 | NQCR | > > > | CA04 | NQCR | > > > | CA02 | MVYK | > > > | CA10 | 0BEC | > > > | AND SO ON. | > > > +-----------+------+ > > > > > > Each list has 25 to 350 customers. Same > > > customer_id may exist in multiple lists. We need > > to > > > compare CA10 list customer_ids with other 11 > > lists > > > to find matching id count by list_name. The > query > > > output should be something similar below: > > > +------+-----------+ > > > | list_name |count | > > > +------+-----------+ > > > | CA05 | 60 | > > > | CA07 | 42 | > > > | CA01 | 35 | > > > | CA03 | 28 | > > > | CA09 | 15 | > > > | AND SO ON | > > > +-----------+------+ > > > > > > Can this be done with a SELECT statement without > > > using perl or php? > > > > > > Thanks in advance for any help. > > > > > > --------------------------------- > > Yahoo! Mail > > Bring photos to life! New PhotoMail makes sharing > a > > breeze. > > > __________________________________________________ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam > protection around > http://mail.yahoo.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > --------------------------------- > Yahoo! Mail > Use Photomail to share photos without annoying attachments. __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
