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:

PC Cookie Problem? (2003) Showif, Hideif reverse logic ? (1997) Searching/sorting dates (1997) Nesting Search Within Tag? (1997) [Sum] function? (1997) includes and cart numbers (1997) 2.1 pricing? (1998) sort by day of week... (2003) WebCat2.0 acgi vs plugin (1997) For those of you not on the WebCatalog Beta... (1997) [append] vs. [appendfile] delta + question? (1997) Mac Vs WindowsNT (1997) Credit Card Number checking (1997) Strange Search Results (2004) Automatic POST arg sending? (1998) Cookies [was How do I get Google to crawl a WebCatsite?] (2003) RE: Nesting [ListFiles] (1998) php vs WebCatalog (2000) Clear command and ShoppingCart.tmpl (1997) Problem (1997)