Re: Date fun with MySQL
This WebDNA talk-list message is from 2004
It keeps the original formatting.
numero = 58474
interpreted = N
texte = Sal, I think we have found the problem. Using this: select DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name from bugs, users where bugs.bug_reporter =3D users.user_id order by mydate DESC Will NOT WORK with WebDNA, but it does so with any other SQL Interpreter. Seems like WebDNA can not interpret the field that DATE_FORMAT has generated, because in the template the [mydate] is not interpreted. I have sent this already to Scott Nitai --=20 Tools to energize your business Web Content Management =ABRedakto=BB & eBusiness Systems ComputerOil=AE AG Dammstrasse 7 5400 Baden / Switzerland Telefon +41 (0)844 44 55 66 info@computeroil.com http://computeroil.com > From: Sal D'Anna
> Reply-To: WebDNA Talk > Date: Thu, 17 Jun 2004 16:50:26 -0700 > To: WebDNA Talk > Subject: Re: Date fun with MySQL >=20 > I am a little confused about your question. The value that is returned i= s > directly from the MySql syntax. Here are some useful results using date = and > time. >=20 > DAYOFWEEK(date expr) > Gets weekday for Date (1 =3D Sunday, 2 =3D Monday, 2 =3D Tuesday ..) This is > according to the ODBC standard. > mysql> select dayofweek('1998-02-03'); -> 3 >=20 > WEEKDAY(date expr) > Gets weekday for Date (0 =3D Monday, 1 =3D Tuesday ..) > mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 > mysql> select WEEKDAY('1997-11-05'); -> 2 >=20 > DAYOFMONTH(date expr) > Returns day of month (1-31) > mysql> select DAYOFMONTH('1998-02-03'); -> 3 >=20 > DAYOFYEAR(date expr) > Returns day of year (1-366) > mysql> select DAYOFYEAR('1998-02-03'); -> 34 >=20 > MONTH(date expr)=20 > Returns month (1-12) > mysql> select MONTH('1998-02-03'); -> 02 >=20 > DAYNAME(date expr) > Returns the name of the day. > mysql> select dayname("1998-02-05"); -> Thursday >=20 > MONTHNAME(date expr) > Returns the name of the month. > mysql> select monthname("1998-02-05"); -> February >=20 > QUARTER(date expr) > Returns quarter (1-4). > mysql> select QUARTER('98-04-01'); -> 2 >=20 > WEEK(date expr)=20 > Returns week (1-53) on locations where Sunday is the first day of the yea= r > mysql> select WEEK('98-02-20'); -> 7 >=20 > YEAR(date expr)=20 > Returns year (1000-9999). > mysql> select YEAR('98-02-03'); -> 1998 >=20 > HOUR(time expr)=20 > Returns hour (0-23) > mysql> select HOUR('10:05:03'); -> 10 >=20 > MINUTE(time expr) > Returns minute (0-59). > mysql> select MINUTE('98-02-03 10:05:03'); -> 5 >=20 > SECOND(time expr) > Returns seconds (1000-9999). > mysql> select SECOND('10:05:03'); -> 3 >=20 > PERIOD_ADD(P, N)=20 > Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM. > mysql> select PERIOD_ADD(9801,2); -> 199803 >=20 > PERIOD_DIFF(A, B) > Returns months between periods A and B. A and B should be of format YYMM = or > YYYYMM.=20 > mysql> select PERIOD_DIFF(9802,199703); -> 11 >=20 > TO_DAYS(Date)=20 > Changes a Date to a daynumber (Number of days since year 0). Date may be = a > DATE string, a DATETIME string, a TIMESTAMP([6 | 8 | 14]) or a number of > format YYMMDD or YYYYMMDD. > mysql> select TO_DAYS(9505); -> 733364 > mysql> select TO_DAYS('1997-10-07); -> 729669 >=20 > FROM_DAYS()=20 > Changes a daynumber to a DATE. > mysql> select from_days(729669); -> 1997-10-07 >=20 > Salvatore D'Anna > DotNetNuke Hosting >=20 >=20 > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of > Nitai @ ComputerOil > Sent: Thursday, June 17, 2004 11:17 AM > To: WebDNA Talk > Subject: Re: Date fun with MySQL >=20 > Now I dont get an error anymore, but the [mydate] field appears like raw. >=20 > I use this: >=20 > select DATE_FORMAT(bugs.mydate, '%d %m %Y'),bugs.id, users.user_name > from bugs, users > where bugs.bug_reporter =3D users.user_id > order by mydate, mytime DESC >=20 > Nitai >=20 > --=20 > Tools to energize your business > Web Content Management & eBusiness Systems >=20 > ComputerOilR AG > Dammstrasse 7 > 5400 Baden / Switzerland > Telefon +41 (0)844 44 55 66 >=20 > info@computeroil.com > http://computeroil.com >=20 >=20 >> From: Sal D'Anna >> Reply-To: WebDNA Talk >> Date: Thu, 17 Jun 2004 10:12:22 -0700 >> To: WebDNA Talk >> Subject: Re: Date fun with MySQL >>=20 >> What's the error? >>=20 >> Salvatore D'Anna >> DotNetNuke Hosting >>=20 >>=20 >>=20 >> -----Original Message----- >> From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of >> Nitai @ ComputerOil >> Sent: Thursday, June 17, 2004 8:14 AM >> To: WebDNA Talk >> Subject: Re: Date fun with MySQL >>=20 >> Question: >>=20 >> Somehow this date_format gives me an erorr when doing joins: >>=20 >> select bugs.id, DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name >> from bugs, users >> where bugs.bug_reporter =3D users.user_id >> order by bug_date, bug_time DESC >=20 >=20 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > __________ NOD32 1.790 (20040617) Information __________ >=20 > This message was checked by NOD32 Antivirus System. > http://www.nod32.com >=20 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
Associated Messages, from the most recent to the oldest:
Sal, I think we have found the problem. Using this: select DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name from bugs, users where bugs.bug_reporter =3D users.user_id order by mydate DESC Will NOT WORK with WebDNA, but it does so with any other SQL Interpreter. Seems like WebDNA can not interpret the field that DATE_FORMAT has generated, because in the template the [mydate] is not interpreted. I have sent this already to Scott Nitai --=20 Tools to energize your business Web Content Management =ABRedakto=BB & eBusiness Systems ComputerOil=AE AG Dammstrasse 7 5400 Baden / Switzerland Telefon +41 (0)844 44 55 66 info@computeroil.com http://computeroil.com > From: Sal D'Anna > Reply-To: WebDNA Talk > Date: Thu, 17 Jun 2004 16:50:26 -0700 > To: WebDNA Talk > Subject: Re: Date fun with MySQL >=20 > I am a little confused about your question. The value that is returned i= s > directly from the MySql syntax. Here are some useful results using date = and > time. >=20 > DAYOFWEEK(date expr) > Gets weekday for Date (1 =3D Sunday, 2 =3D Monday, 2 =3D Tuesday ..) This is > according to the ODBC standard. > mysql> select dayofweek('1998-02-03'); -> 3 >=20 > WEEKDAY(date expr) > Gets weekday for Date (0 =3D Monday, 1 =3D Tuesday ..) > mysql> select WEEKDAY('1997-10-04 22:23:00'); -> 5 > mysql> select WEEKDAY('1997-11-05'); -> 2 >=20 > DAYOFMONTH(date expr) > Returns day of month (1-31) > mysql> select DAYOFMONTH('1998-02-03'); -> 3 >=20 > DAYOFYEAR(date expr) > Returns day of year (1-366) > mysql> select DAYOFYEAR('1998-02-03'); -> 34 >=20 > MONTH(date expr)=20 > Returns month (1-12) > mysql> select MONTH('1998-02-03'); -> 02 >=20 > DAYNAME(date expr) > Returns the name of the day. > mysql> select dayname("1998-02-05"); -> Thursday >=20 > MONTHNAME(date expr) > Returns the name of the month. > mysql> select monthname("1998-02-05"); -> February >=20 > QUARTER(date expr) > Returns quarter (1-4). > mysql> select QUARTER('98-04-01'); -> 2 >=20 > WEEK(date expr)=20 > Returns week (1-53) on locations where Sunday is the first day of the yea= r > mysql> select WEEK('98-02-20'); -> 7 >=20 > YEAR(date expr)=20 > Returns year (1000-9999). > mysql> select YEAR('98-02-03'); -> 1998 >=20 > HOUR(time expr)=20 > Returns hour (0-23) > mysql> select HOUR('10:05:03'); -> 10 >=20 > MINUTE(time expr) > Returns minute (0-59). > mysql> select MINUTE('98-02-03 10:05:03'); -> 5 >=20 > SECOND(time expr) > Returns seconds (1000-9999). > mysql> select SECOND('10:05:03'); -> 3 >=20 > PERIOD_ADD(P, N)=20 > Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM. > mysql> select PERIOD_ADD(9801,2); -> 199803 >=20 > PERIOD_DIFF(A, B) > Returns months between periods A and B. A and B should be of format YYMM = or > YYYYMM.=20 > mysql> select PERIOD_DIFF(9802,199703); -> 11 >=20 > TO_DAYS(Date)=20 > Changes a Date to a daynumber (Number of days since year 0). Date may be = a > DATE string, a DATETIME string, a TIMESTAMP([6 | 8 | 14]) or a number of > format YYMMDD or YYYYMMDD. > mysql> select TO_DAYS(9505); -> 733364 > mysql> select TO_DAYS('1997-10-07); -> 729669 >=20 > FROM_DAYS()=20 > Changes a daynumber to a DATE. > mysql> select from_days(729669); -> 1997-10-07 >=20 > Salvatore D'Anna > DotNetNuke Hosting >=20 >=20 > -----Original Message----- > From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of > Nitai @ ComputerOil > Sent: Thursday, June 17, 2004 11:17 AM > To: WebDNA Talk > Subject: Re: Date fun with MySQL >=20 > Now I dont get an error anymore, but the [mydate] field appears like raw. >=20 > I use this: >=20 > select DATE_FORMAT(bugs.mydate, '%d %m %Y'),bugs.id, users.user_name > from bugs, users > where bugs.bug_reporter =3D users.user_id > order by mydate, mytime DESC >=20 > Nitai >=20 > --=20 > Tools to energize your business > Web Content Management & eBusiness Systems >=20 > ComputerOilR AG > Dammstrasse 7 > 5400 Baden / Switzerland > Telefon +41 (0)844 44 55 66 >=20 > info@computeroil.com > http://computeroil.com >=20 >=20 >> From: Sal D'Anna >> Reply-To: WebDNA Talk >> Date: Thu, 17 Jun 2004 10:12:22 -0700 >> To: WebDNA Talk >> Subject: Re: Date fun with MySQL >>=20 >> What's the error? >>=20 >> Salvatore D'Anna >> DotNetNuke Hosting >>=20 >>=20 >>=20 >> -----Original Message----- >> From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf Of >> Nitai @ ComputerOil >> Sent: Thursday, June 17, 2004 8:14 AM >> To: WebDNA Talk >> Subject: Re: Date fun with MySQL >>=20 >> Question: >>=20 >> Somehow this date_format gives me an erorr when doing joins: >>=20 >> select bugs.id, DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name >> from bugs, users >> where bugs.bug_reporter =3D users.user_id >> order by bug_date, bug_time DESC >=20 >=20 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 > __________ NOD32 1.790 (20040617) Information __________ >=20 > This message was checked by NOD32 Antivirus System. > http://www.nod32.com >=20 >=20 >=20 > ------------------------------------------------------------- > This message is sent to you because you are subscribed to > the mailing list . > To unsubscribe, E-mail to: > To switch to the DIGEST mode, E-mail to > > Web Archive of this list is at: http://webdna.smithmicro.com/ >=20 ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
"Nitai @ ComputerOil"
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
WebCat2 beta 11 - new prefs ... (1997)
restart needed???? (1997)
[WebDNA] WSC Investors Repository (2008)
Re:appending to the database (1999)
Showif date range comparison (1999)
Cart Numbers (1997)
Re[3]: 2nd WebCatalog2 Feature Request (1996)
webcat crashes when using groups (1999)
Textarea Breaks (2000)
Loop context to replace items (1998)
WebCommerce: Folder organization ? (1997)
referrer usage (1997)
RE: automatic reload of frameset (1997)
Size/Color Options (2002)
RE: new cart IDs being assigned somehow (1997)
Creating main- and sub-category search (1997)
Data Problem (2004)
[dos] command (1997)
Help: sorting in found set. (2001)
Seeking NT Mail Server Experiences (1998)