[Zope] Newbie date/time + MySQL formatting problem
Eric L. Walstad
ewalstad@energywright.com
Thu, 04 May 2000 08:45:43 -0700
Hi Jeff,
I think you may already have this one figured out, but just incase you
don't...
Here's the ZSQL I use to search a user table. Note that it returns the
user's birthday in the format you need. Note also that the formatting is
done by MySQL BEFORE the results get to Zope:
SELECT CONCAT(FirstName, " ", LastName) as Name, Address, City, State, Zip,
Phone, Fax, email, URL, DATE_FORMAT(Birthday, '%M %D, %Y') AS BDay
FROM users
WHERE
FirstName LIKE "%<dtml-var FirstName>%"
AND
LastName LIKE "%<dtml-var LastName>%"
ORDER BY LastName, FirstName
The key here is the "DATE_FORMAT()" function. There are a ton of parameters
that can be passed to this function to get just the formatting you need.
The format I'm using here returns something like "January 18th, 1958"
Here's a link to the searchable MySQL help system. Just search for
"DATE_FORMAT" and you'll find a table of all the parameters available and an
explanation of what they do:
http://www.mysql.com/php/manual.php3
// -----Original Message-----
// From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Mike
// Orr
// Sent: Thursday, May 04, 2000 8:18 AM
// To: zope@zope.org
// Subject: Re: [Zope] Newbie date/time + MySQL problem
//
//
// It is returning a string. I had that problem too. It's another of
// those DateTime/path problems --- MySQLdb returns a date outside
// Zope but a
// string inside Zope.
//
//
// -Mike Orr, mso@mso.oz.net -or- mso@oz.net (permanent: mso@jimpick.com)
// http://mso.oz.net/ English * Esperanto * Russkiy *
// Deutsch * Espan~ol
//
// On Tue, Apr 25, 2000 at 01:11:48PM -0500, jeffr@odeon.net wrote:
// >
// > Hmm, that didn't work. I get a Zope error of:
// >
// > Error Type: TypeError
// > Error Value: not all arguments converted
// >
// > What I think is the relevent portion of the traceback shows:
// >
// > File /usr/local/Zope/lib/python/DocumentTemplate/DT_Var.py,
// line 313, in render
// > (Object: date)
// > (Info: ('date', '2000-04-25', {'null': '', 'fmt':
// 'aCommon', '': 'date'}))
// >
// >
// > I think the problem is the string returned from MySQL isn't a
// > date object as far as Zope is concerned.
// >
// > Any help is appreciated,
// >
// > Jeff
// >
// >
// > On Tue, 25 Apr 2000, Yanko, Curtis (GSP) wrote:
// >
// > > Just figured this one out myself a week or two ago. I use
// something like
// > > this:
// > >
// > > <dtml-var date fmt=aCommon>
// > >
// > > If you are using a Zope Date/Time object, this should work.
// > >
// > > -Curt
// > >
// > > > -----Original Message-----
// > > > From: jeffr@odeon.net [SMTP:jeffr@odeon.net]
// > > > Sent: Tuesday, April 25, 2000 12:02 PM
// > > > To: zope@zope.org
// > > > Subject: [Zope] Newbie date/time + MySQL problem
// > > >
// > > >
// > > > Greetings folks,
// > > >
// > > > I've got a record in an SQL database that contains a date
// > > > field (2000-02-25 for example).
// > > >
// > > > I'm generating a table from an SQL query. The bit
// that displays
// > > > the date is pretty simple: <dtml-var date null="">
// > > >
// > > > I want to format the date into something a little
// more readable
// > > > (like 'Feb 25, 2000' for example). I've been
// trying to figure out
// > > > how to use the date-time format functions mentioned
// in the DTML
// > > > guide in Appendix A. It mentions the special
// variable _, which
// > > > provides a method for constructing date time values
// from strings
// > > > and numeric data.
// > > >
// > > > I think my question is really a matter of syntax,
// as the DTML
// > > > guide doesn't provide any examples of usage in Appendix A.
// > > >
// > > > Would I use something like: <dtml-var
// _.aCommon(date) null=""> ?
// > > >
// > > > Any help would be greatly appreciated,
// > > >
// > > > Jeff
// > > >
// > > >
// > > > _______________________________________________
// > > > Zope maillist - Zope@zope.org
// > > > http://lists.zope.org/mailman/listinfo/zope
// > > > ** No cross posts or HTML encoding! **
// > > > (Related lists -
// > > > http://lists.zope.org/mailman/listinfo/zope-announce
// > > > http://lists.zope.org/mailman/listinfo/zope-dev )
// > >
// > > _______________________________________________
// > > Zope maillist - Zope@zope.org
// > > http://lists.zope.org/mailman/listinfo/zope
// > > ** No cross posts or HTML encoding! **
// > > (Related lists -
// > > http://lists.zope.org/mailman/listinfo/zope-announce
// > > http://lists.zope.org/mailman/listinfo/zope-dev )
// > >
// >
// >
// > _______________________________________________
// > Zope maillist - Zope@zope.org
// > http://lists.zope.org/mailman/listinfo/zope
// > ** No cross posts or HTML encoding! **
// > (Related lists -
// > http://lists.zope.org/mailman/listinfo/zope-announce
// > http://lists.zope.org/mailman/listinfo/zope-dev )
// >
//
// --
// -Mike Orr, mso@mso.oz.net -or- mso@oz.net (permanent: mso@jimpick.com)
// http://mso.oz.net/ English * Esperanto * Russkiy *
// Deutsch * Espan~ol
//
// _______________________________________________
// Zope maillist - Zope@zope.org
// http://lists.zope.org/mailman/listinfo/zope
// ** No cross posts or HTML encoding! **
// (Related lists -
// http://lists.zope.org/mailman/listinfo/zope-announce
// http://lists.zope.org/mailman/listinfo/zope-dev )
//