Re: YEAR and time types

[hsv]

 2012/07/24 13:07 -0700, Hassan Schroeder 
On Tue, Jul 24, 2012 at 10:36 AM, Roberta Jaskólski h...@tbbs.net wrote:

  version(): 5.5.8-log
 @@sql_mode: 
 REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

 What is yours?

version  5.5.19
@@sql_mode is empty

Well, the answer to my original question is that MySQL is willind with a 
warning to narrow DATE to YEAR, but with my usual setting, with 
STRICT_TRANS_TABLES, the warning becomes an error. As for widening YEAR to 
DATE, MySQL never does it: with a warning it is willind to return the 0-date 
for an attempt at widening, but, with STRICT_TRANS_TABLES, as erst, the warning 
becomes an error. It would be better if MySQL were willind also to widen: if, 
say, the YEAR were 1967, it would be widened to 1967-00-00. Too bad.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: YEAR and time types

[hsv]

 2012/07/23 11:54 -0700, Rick James 
mysql SELECT YEAR(NOW());
+-+
| YEAR(NOW()) |
+-+
|2012 |
+-+

mysql SELECT CONCAT('2012', '-01-01');
+--+
| CONCAT('2012', '-01-01') |
+--+
| 2012-01-01   |
+--+

To show that it acts like a DATE:
mysql SELECT CONCAT('2012', '-01-01') - INTERVAL 1 day;
+---+
| CONCAT('2012', '-01-01') - INTERVAL 1 day |
+---+
| 2011-12-31|
+---+

No: I mean variables of type YEAR


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: YEAR and time types

[Roberta Jask�lski]

 2012/07/24 08:46 -0700, Hassan Schroeder 
On Mon, Jul 23, 2012 at 5:58 AM,  h...@tbbs.net wrote:

 I thought I would try YEAR to record it--but, in spite of 
 http://dev.mysql.com/doc/refman/5.5/en/year.html , simply assigning NOW() to 
 such a type does not work.

Not sure I understand the issue:

mysql create table babble (sometime year(4));
Query OK, 0 rows affected (0.13 sec)

mysql show create table babble;
+++
| Table  | Create Table
   |
+++
| babble | CREATE TABLE `babble` (
  `sometime` year(4) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+++
1 row in set (0.00 sec)

mysql insert into babble values ( NOW() );
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql select * from babble;
+--+
| sometime |
+--+
| 2012 |
+--+

??

Well, let me show this:

 version(): 5.5.8-log
@@sql_mode: 
REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_DATE,ALLOW_INVALID_DATES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

What is yours?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: YEAR and time types

[Rick James]

mysql SELECT YEAR(NOW());
+-+
| YEAR(NOW()) |
+-+
|2012 |
+-+

mysql SELECT CONCAT('2012', '-01-01');
+--+
| CONCAT('2012', '-01-01') |
+--+
| 2012-01-01   |
+--+

To show that it acts like a DATE:
mysql SELECT CONCAT('2012', '-01-01') - INTERVAL 1 day;
+---+
| CONCAT('2012', '-01-01') - INTERVAL 1 day |
+---+
| 2011-12-31|
+---+


 -Original Message-
 From: h...@tbbs.net [mailto:h...@tbbs.net]
 Sent: Monday, July 23, 2012 5:59 AM
 To: mysql@lists.mysql.com
 Subject: YEAR and time types
 
 A director s term ends in a given year, but at no given time of year;
 depends on the yearly meeting.
 
 I thought I would try YEAR to record it--but, in spite of
 http://dev.mysql.com/doc/refman/5.5/en/year.html , simply assigning
 NOW() to such a type does not work. There is also no implicit
 conversion to DATE. All in all, it behaves as a small integer, not a
 time type. For my end it is much less good than 'year-00-00', something
 already slightly obscure.
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql