The aplication which is writing to the database is sending the date in unix format. I can't no change that, so i suposse using a triger will help.
The application pass the 'xxxxxx.xx' value when is inserting to the table. ----- "Martijn Tonies" <[EMAIL PROTECTED]> escribió: > >If i define that the value for the field must be varchar data type, > `fecha` > varchar(25) default NULL, the triger records the right date. > >I think because is wrinting a string only. But if i define the field > as a > date data type, `fecha` date default NULL, the value writen is: > 1969-31-12. > I suppose this is >because the table is waiting for a date data type, > but > the triger sends a string data type. > > When you send an INSERT statement to the server, what value are you > passing > for > the date column? > > If you want to store a DATE, send a date! Don't send a "unixtime", or > a > "varchar" in > some format, send a date. > > Why on earth would you go sending a "unixtime" to the server and then > using > a trigger > to convert it into a "real date"?! > > >im correct? if im, should CAST help me? > > No, sending the proper value, that's what you should be doing. > > And storing a date-value in a DATE datatype will only be more > convenient > later on. > > Martijn Tonies > Database Workbench - development tool for MySQL, and more! > Upscene Productions > http://www.upscene.com > My thoughts: > http://blog.upscene.com/martijn/ > Database development questions? Check the forum! > http://www.databasedevelopmentforum.com > > > > I have a problem with a trigger which should conver a unix > timestamp > > to a > > MySQL date datatype. > > > The trigger works if the column is varchar, but when the column > is > > date > > type, it write the date of 1969-31-12. > > > > Instead of "column", I guess you mean "value"? > > > > > Any ideas? > > > > > > > > > > > > DROP TABLE IF EXISTS `visitas`; > > > CREATE TABLE `visitas` ( > > > `id` int(11) NOT NULL auto_increment, > > > `date` varchar(25) default NULL, > > > > If you want to store a date-value, use a date-datatype, not a > > character > > based datatype. > > > > > `elapsed` int default NULL, > > > `src_ip` varchar(15) default NULL, > > > `result_code` varchar(25) default NULL, > > > `http_status` TINYINT default NULL, > > > `bytes` int default NULL, > > > `request` varchar(50) default NULL, > > > `authname` varchar(10) default NULL, > > > `type` varchar(20) default NULL, > > > PRIMARY KEY (`id`) > > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1; > > > > > > /*!50003 SET @OLD_SQL_MODE=@@SQL_MODE*/; > > > DELIMITER ;; > > > /*!50003 SET SESSION SQL_MODE="STRICT_TRANS_TABLES" */;; > > > /*!50003 CREATE */ /*!50017 [EMAIL PROTECTED] */ > /*!50003 > > TRIGGER > > `unix2normaltime` BEFORE INSERT ON `visitas` FOR EACH ROW begin > > > set New.date=date(from_unixtime(New.date)); > > > end */;; > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
