INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Incorrect datetime value:

Incorrect datetime value:

(OP)
Hello all

I moved my server to a new VPS box. I am trying to upload the database sql that I exported from the old site but I keep getting errors that I do not know how to remedy.

THis is on mysql database.

CODE

Error
SQL query:



--

INSERT INTO `orders` (`id`, `tracking_`, `source_`, `istatus_`, `case_`, `cus_id`, `ip_`, 

`device_id`, `mod1_`, `brand_`, `carrier_`, `model_`, `off_`, `value_`, `first_`, `last_`, `phone_`, `address1_`, `address2_`, `city_`, `state_`, 

`zip_`, `method_`, `payment_`, `email_`, `image_`, `confirm_`, `date_`, `payment_date`, `paypalemail_`, `item_grade_`, `item_comments_`, `istatus2_`, 

`selling_price_`, `profit_`, `imei_`, `imei_status_`, `sell_date_`, `con_dif_`, `update_`, `emails_sent`, `emails_sent2`) VALUES


values not posted due to security


MySQL said: Documentation

#1292 - Incorrect datetime value: '2014-

06-29 20:11:48' for column 'date_' at row 40 

This came from the same export so why should the import be any different? The field is set to date to timestamp with current time as the default. I tried changing it to just datetime but it still will not populate the table.

Does anyone have any ideas on what I should do?

RE: Incorrect datetime value:

i think it would help to see the values.... i.e. the full query. asterisk out field values that are sensitive.

RE: Incorrect datetime value:

(OP)
Ok, give me a bit and I will repost

RE: Incorrect datetime value:

(OP)
Here are 2 of the entries. All of them have the same format obviously.

CODE

(100, '607001725103', '', 'Cancel', 'open', 0, '', 163, 'phone', 'Damaged', NULL, 

NULL, NULL, '2.40', 'Ja***', 'G***', '', '1*** ki** circle apt. **', '', 'chicago', 'IL', '60***', 'kit_', 'check', '***********@gmail.com', 

NULL, 1325721, '2014-06-24 17:34:21', '0000-00-00', '', '', '', '', '0.00', '0.00', '', '', '0000-00-00 00:00:00', 'no', '0000-00-00 00:00:00', 0, 

0),
(103, '', '', 'Cancel', 'open', 0, '', 399, 'phone', 'Good/Used', NULL, NULL, NULL, '0.00', 'D*****', 'H*******', '8328947276', '****** 1960 W.', 

'', '*******', 'TX', '******', 'email_', 'check', 'den********@gmail.com', NULL, 1325722, '2014-06-24 18:08:39', '0000-00-00', '', '', '', '', 

'0.00', '0.00', '', '', '0000-00-00 00:00:00', 'no', '0000-00-00 00:00:00', 0, 0) 

hope this helps

RE: Incorrect datetime value:

intriguing. looks ok to me.

can you post the schema and let us know the mysql version number.

also - how are you exporting/importing this? via mysqldump and the < ?

RE: Incorrect datetime value:

(OP)
I have tried inporting the sql file as well as just pasting the sql.

let me know if this is not what you are looking for

CODE

-- phpMyAdmin SQL Dump
-- version 3.5.6
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Aug 05, 2014 at 08:54 AM
-- Server version: 5.1.56-community
-- PHP Version: 5.3.10

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- 

RE: Incorrect datetime value:

(OP)
here is the rest of the sql that I left off

CODE

Table structure for table `orders`
--

CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `tracking_` varchar(40) NOT NULL,
  `source_` varchar(20) NOT NULL,
  `istatus_` varchar(30) NOT NULL DEFAULT 'New Order',
  `case_` varchar(100) NOT NULL DEFAULT 'open',
  `cus_id` int(11) NOT NULL,
  `ip_` varchar(20) NOT NULL,
  `device_id` int(11) NOT NULL,
  `mod1_` varchar(20) NOT NULL,
  `brand_` varchar(14) DEFAULT NULL,
  `carrier_` varchar(8) DEFAULT NULL,
  `model_` varchar(40) DEFAULT NULL,
  `off_` varchar(3) DEFAULT NULL,
  `value_` decimal(5,2) DEFAULT NULL,
  `first_` varchar(100) NOT NULL,
  `last_` varchar(100) NOT NULL,
  `phone_` varchar(20) NOT NULL,
  `address1_` varchar(100) DEFAULT NULL,
  `address2_` varchar(100) DEFAULT NULL,
  `city_` varchar(100) NOT NULL,
  `state_` varchar(100) NOT NULL,
  `zip_` varchar(5) NOT NULL,
  `method_` varchar(10) NOT NULL,
  `payment_` varchar(10) NOT NULL,
  `email_` varchar(100) NOT NULL,
  `image_` int(10) DEFAULT NULL,
  `confirm_` int(11) NOT NULL,
  `date_` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `payment_date` date NOT NULL,
  `paypalemail_` varchar(100) NOT NULL DEFAULT 'none',
  `item_grade_` varchar(30) NOT NULL,
  `item_comments_` text NOT NULL,
  `istatus2_` varchar(30) NOT NULL,
  `selling_price_` decimal(5,2) NOT NULL,
  `profit_` decimal(5,2) NOT NULL,
  `imei_` varchar(30) NOT NULL,
  `imei_status_` varchar(30) NOT NULL,
  `sell_date_` datetime NOT NULL,
  `con_dif_` varchar(10) NOT NULL DEFAULT 'no',
  `update_` datetime NOT NULL,
  `emails_sent` int(11) NOT NULL DEFAULT '0',
  `emails_sent2` int(11) NOT NULL DEFAULT '0',
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=486 ;

--
-- Dumping data for table `orders` 

RE: Incorrect datetime value:

i have recreated the database and the table. the string imports just fine on my machine.

RE: Incorrect datetime value:

(OP)
Seriously weird man!

I do not know what else to do at this point.

RE: Incorrect datetime value:

I have to agree with jpadie.

It imports without any errors from the command line:

mysql test -u username -p < /path/to/file/sqlinsert.sql

I can also insert it fine directly in mysql. Are you using a GUI to do this, or directly on the command prompt?

Also is what you posted what's on line 40 of your dump? If not what exactly is there?



----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

RE: Incorrect datetime value:

(OP)
Weirdest thing. I went home, ate, slept.
Came back this morning and put the SQL in for the population of the table and it went in without a hitch.

Not sure what was different or why it worked.

Thanks everyone for the help and sorry I cannot give an explaination.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close