Re: Date fun with MySQL

This WebDNA talk-list message is from

2004

It keeps the original formatting. numero = 58473
interpreted = N
texte = I am a little confused about your question. The value that is returned isdirectly from the MySql syntax. Here are some useful results using date andtime.DAYOFWEEK(date expr) Gets weekday for Date (1 = Sunday, 2 = Monday, 2 = Tuesday ..) This isaccording to the ODBC standard. mysql> select dayofweek('1998-02-03');-> 3WEEKDAY(date expr) Gets weekday for Date (0 = Monday, 1 = Tuesday ..) mysql> select WEEKDAY('1997-10-04 22:23:00');-> 5mysql> select WEEKDAY('1997-11-05'); -> 2DAYOFMONTH(date expr) Returns day of month (1-31) mysql> select DAYOFMONTH('1998-02-03');-> 3DAYOFYEAR(date expr) Returns day of year (1-366) mysql> select DAYOFYEAR('1998-02-03');-> 34MONTH(date expr) Returns month (1-12) mysql> select MONTH('1998-02-03');-> 02DAYNAME(date expr) Returns the name of the day. mysql> select dayname("1998-02-05"); -> ThursdayMONTHNAME(date expr) Returns the name of the month. mysql> select monthname("1998-02-05"); -> FebruaryQUARTER(date expr) Returns quarter (1-4). mysql> select QUARTER('98-04-01');-> 2WEEK(date expr) Returns week (1-53) on locations where Sunday is the first day of the year mysql> select WEEK('98-02-20');-> 7YEAR(date expr) Returns year (1000-9999). mysql> select YEAR('98-02-03');-> 1998HOUR(time expr) Returns hour (0-23) mysql> select HOUR('10:05:03');-> 10MINUTE(time expr) Returns minute (0-59). mysql> select MINUTE('98-02-03 10:05:03');-> 5SECOND(time expr) Returns seconds (1000-9999). mysql> select SECOND('10:05:03');-> 3PERIOD_ADD(P, N) Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM. mysql> select PERIOD_ADD(9801,2); -> 199803PERIOD_DIFF(A, B) Returns months between periods A and B. A and B should be of format YYMM orYYYYMM. mysql> select PERIOD_DIFF(9802,199703); -> 11TO_DAYS(Date) Changes a Date to a daynumber (Number of days since year 0). Date may be aDATE string, a DATETIME string, a TIMESTAMP([6 | 8 | 14]) or a number offormat YYMMDD or YYYYMMDD. mysql> select TO_DAYS(9505); -> 733364mysql> select TO_DAYS('1997-10-07); -> 729669FROM_DAYS() Changes a daynumber to a DATE. mysql> select from_days(729669); -> 1997-10-07 Salvatore D'AnnaDotNetNuke Hosting-----Original Message-----From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf OfNitai @ ComputerOilSent: Thursday, June 17, 2004 11:17 AMTo: WebDNA TalkSubject: Re: Date fun with MySQLNow I dont get an error anymore, but the [mydate] field appears like raw.I use this:select DATE_FORMAT(bugs.mydate, '%d %m %Y'),bugs.id, users.user_namefrom bugs, userswhere bugs.bug_reporter = users.user_idorder by mydate, mytime DESCNitai-- Tools to energize your businessWeb Content Management & eBusiness SystemsComputerOilR AGDammstrasse 75400 Baden / SwitzerlandTelefon +41 (0)844 44 55 66info@computeroil.comhttp://computeroil.com> 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> > What's the error?> > Salvatore D'Anna> DotNetNuke Hosting> > > > -----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> > Question:> > Somehow this date_format gives me an erorr when doing joins:> > select bugs.id, DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name> from bugs, users> where bugs.bug_reporter = users.user_id> order by bug_date, bug_time DESC-------------------------------------------------------------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 toWeb Archive of this list is at: http://webdna.smithmicro.com/__________ NOD32 1.790 (20040617) Information __________This message was checked by NOD32 Antivirus System.http://www.nod32.com-------------------------------------------------------------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:

Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( Gary Krockover 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( Gary Krockover 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( "Sal D'Anna" 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( "Sal D'Anna" 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( "Sal D'Anna" 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( eLists 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Re: Date fun with MySQL ( Alain Russell 2004) Re: Date fun with MySQL ( "Nitai @ ComputerOil" 2004) Date fun with MySQL ( "Nitai @ ComputerOil" 2004)

I am a little confused about your question. The value that is returned isdirectly from the MySql syntax. Here are some useful results using date andtime.DAYOFWEEK(date expr) Gets weekday for Date (1 = Sunday, 2 = Monday, 2 = Tuesday ..) This isaccording to the ODBC standard. mysql> select dayofweek('1998-02-03');-> 3WEEKDAY(date expr) Gets weekday for Date (0 = Monday, 1 = Tuesday ..) mysql> select WEEKDAY('1997-10-04 22:23:00');-> 5mysql> select WEEKDAY('1997-11-05'); -> 2DAYOFMONTH(date expr) Returns day of month (1-31) mysql> select DAYOFMONTH('1998-02-03');-> 3DAYOFYEAR(date expr) Returns day of year (1-366) mysql> select DAYOFYEAR('1998-02-03');-> 34MONTH(date expr) Returns month (1-12) mysql> select MONTH('1998-02-03');-> 02DAYNAME(date expr) Returns the name of the day. mysql> select dayname("1998-02-05"); -> ThursdayMONTHNAME(date expr) Returns the name of the month. mysql> select monthname("1998-02-05"); -> FebruaryQUARTER(date expr) Returns quarter (1-4). mysql> select QUARTER('98-04-01');-> 2WEEK(date expr) Returns week (1-53) on locations where Sunday is the first day of the year mysql> select WEEK('98-02-20');-> 7YEAR(date expr) Returns year (1000-9999). mysql> select YEAR('98-02-03');-> 1998HOUR(time expr) Returns hour (0-23) mysql> select HOUR('10:05:03');-> 10MINUTE(time expr) Returns minute (0-59). mysql> select MINUTE('98-02-03 10:05:03');-> 5SECOND(time expr) Returns seconds (1000-9999). mysql> select SECOND('10:05:03');-> 3PERIOD_ADD(P, N) Adds N months to period P (of type YYMM or YYYYMM). Returns YYYYMM. mysql> select PERIOD_ADD(9801,2); -> 199803PERIOD_DIFF(A, B) Returns months between periods A and B. A and B should be of format YYMM orYYYYMM. mysql> select PERIOD_DIFF(9802,199703); -> 11TO_DAYS(Date) Changes a Date to a daynumber (Number of days since year 0). Date may be aDATE string, a DATETIME string, a TIMESTAMP([6 | 8 | 14]) or a number offormat YYMMDD or YYYYMMDD. mysql> select TO_DAYS(9505); -> 733364mysql> select TO_DAYS('1997-10-07); -> 729669FROM_DAYS() Changes a daynumber to a DATE. mysql> select from_days(729669); -> 1997-10-07 Salvatore D'AnnaDotNetNuke Hosting-----Original Message-----From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com] On Behalf OfNitai @ ComputerOilSent: Thursday, June 17, 2004 11:17 AMTo: WebDNA TalkSubject: Re: Date fun with MySQLNow I dont get an error anymore, but the [mydate] field appears like raw.I use this:select DATE_FORMAT(bugs.mydate, '%d %m %Y'),bugs.id, users.user_namefrom bugs, userswhere bugs.bug_reporter = users.user_idorder by mydate, mytime DESCNitai-- Tools to energize your businessWeb Content Management & eBusiness SystemsComputerOilR AGDammstrasse 75400 Baden / SwitzerlandTelefon +41 (0)844 44 55 66info@computeroil.comhttp://computeroil.com> 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> > What's the error?> > Salvatore D'Anna> DotNetNuke Hosting> > > > -----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> > Question:> > Somehow this date_format gives me an erorr when doing joins:> > select bugs.id, DATE_FORMAT(bugs.mydate, '%d %m %Y'), users.user_name> from bugs, users> where bugs.bug_reporter = users.user_id> order by bug_date, bug_time DESC-------------------------------------------------------------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 toWeb Archive of this list is at: http://webdna.smithmicro.com/__________ NOD32 1.790 (20040617) Information __________This message was checked by NOD32 Antivirus System.http://www.nod32.com-------------------------------------------------------------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/ "Sal D'Anna"

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:

Single Link browsing (1997) ISAPI WC6a and Windows 2003 Web Edition (2006) Date problems (1997) Auto Wrap Text in

(2004) Simple question (1998) URL too Long? (1997) RequiredFields notes ... (1997) Need relative path explanation (1997) Database not found in Include (2002) EditPlus 2 WebDNA cliptext (2002) Forms Search Questions (1997) search engine friendly URLS (Mac) and Lycos! (1998) ReadDateFormat bug in 3.0.1 (1998) A multi-processor savvy WebCatalog? (1997) Page Breaks (1999) Hiding HTML and page breaks (1997) [WebDNA] Screen Resolution - detection & redirect (2012) displaying New products (using [date]) (1997) Car Database (2002) Writing to PDF (2003)