• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 613
  • Last Modified:

mySQL Date format part 2

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 format 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: 1292. Incorrect datetime value: '01/07/2012 00:00:00' for column 'PROCESSED_MONTH'

How do I alter the column structure on the database such that it accepts a date in this format?
0
stummj
Asked:
stummj
  • 5
  • 2
1 Solution
 
johanntagleCommented:
How do I alter the column structure on the database such that it accepts a date in this format?

You don't.

In your previous question I already showed you the str_to_date function.  You use that so that a string that does not follow the standard yyyy-mm-dd format can be imported to a date column.  You just needed to add the formatting parameters so that the time part of your string can be recognized.  In your previous question I showed that you can actually use substring() to remove the time part first, then use str_to_date to read the resulting shortened string.  So now we want to try to purely use str_to_date.  The following should do the trick:


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=str_to_date(@PROCESSED_MONTH,'%m/%d/%Y %H:%i:%s');

See http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format for an explanation of the formatting parameters used.
0
 
stummjAuthor Commented:
Ok thanks - unfortunately your previous comment doesnt work (get an error about system variable not being recognised) but will check the documentation. I think there is a way to mask this column at table level as if I do a describe on it, there appears to be some default formatting on other columns... im just not sure how to do an "ALTER" to convert it!
0
 
stummjAuthor Commented:
I think I have an extra ";" - just retesting...
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
stummjAuthor Commented:
Right thats working now thank you. But only if I use the SET command to change the PROCESSED_DATE. How do I do the TRAFFIC_DATE? I think I need to do it at the same time as the PROCESED_DATE but cant find the syntax in the docs to do multiple SETS!
0
 
stummjAuthor Commented:
SOrted - I was using the correct syntax, but had applied a mask at table level. When I removed that all was well.

There are now only 64 warnings to plough through (from half a million rows so not too bad!) thanks for you help
0
 
stummjAuthor Commented:
Thank you
0
 
johanntagleCommented:
Glad to help and to see you were able to figure out the rest - was already asleep when you replied.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now