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 <yesmin25@stripped> 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 <josh2780@stripped> 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/mysql?unsub=1
>
>
>
>
> ---------------------------------
> 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
