[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

mySQL Date Format

Posted on 2012-08-23
6
Medium Priority
?
506 Views
Last Modified: 2012-08-23
Hello Experts

I have inherited some code that no longer works following a re-install of mySQL (a more recent version than the code was originally used for).

The issue seems to be surrounding the length of a date field that I am importing from a text file.

The code is:

LOAD DATA INFILE 'C:/File.txt' INTO TABLE tbl_tmp
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(PROCESSED_MONTH, TRAFFIC_MONTH, NETWORK_OPERATOR);

The data is this:

Processed BLP Start Date      BLP Start Date      BNOP Name
01/07/2012 00:00:00      01/05/2012 00:00:00      BT
01/07/2012 00:00:00      01/06/2012 00:00:00      O2
01/07/2012 00:00:00      01/06/2012 00:00:00      O2

The error is this:
Error Code: 1406. Data too long for column 'PROCESSED_MONTH' at row 1

By messing with the data, I have pretty good evidence that if the 00:00:00 is removed from the date fields, then all is well.

How can I make the time bit of the string get ignored during the import? I wonder if there is a parameter somewhere in the mySQL config, or whether I need to change the code somehow?
0
Comment
Question by:stummj
  • 3
  • 3
6 Comments
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 38324105
So all the data are being imported as strings, and PROCESSED_MONTH datatype is string, not date?  If so something like this should do it:

LOAD DATA INFILE 'C:/File.txt' INTO TABLE tbl_tmp
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 1 LINES
(@PROCESSED_MONTH, TRAFFIC_MONTH, NETWORK_OPERATOR);
set PROCESSED_MONTH = substring(@PROCESSED_MONTH, 1, 10);

Note: untested.
0
 

Author Comment

by:stummj
ID: 38324116
Good question! Its a varchar2(10) with a default mask of 0000-00-00. Does that imply something needs to happen with those slashes in the file?
0
 

Author Comment

by:stummj
ID: 38324128
Actually - I think the correct thing to do here is to change the data type to "date"
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 24

Expert Comment

by:johanntagle
ID: 38324129
A mask of 0000-00-00 does imply the preferred input is in the standard yyyy-mm-dd format.  Maybe you can also use the str_to_date function after stripping off the time.  something like

set PROCESSED_MONTH=str_to_date(substring(@PROCESSED_MONTH, 1, 10), '%m/%d/%Y')

see http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date
0
 
LVL 24

Expert Comment

by:johanntagle
ID: 38324157
Yes I definitely agree re changing it to date type.  You can still use the str_to_date and substring combination for it, or just str_to_date with additional formatting parameters to handle the time part of the string.
0
 

Author Closing Comment

by:stummj
ID: 38325311
Thank you
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month19 days, 5 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question