Alternatively, you might be able to re-render times and dates in their "condensed" format by auto-converting them to a numeric value. Try adding zero to your date columns in your select clauses. Once condensed, your substring code should begin working as before.
SELECT datecol +0 as datecol FROM ... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Josh <[EMAIL PROTECTED]> wrote on 03/13/2006 12:42:32 PM: > 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_id’s 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] >
