Date formatting CSV MySQL PHP

Question

I have imported a series of CSV files into MySQL. The date columns are all formatting like:

STR_TO_DATE(colName, '%m/%d/%Y') does not work and returns only nulls. How can I format these to a date?

So, if you were to encounter 1112015 – would that be the 11th of January, or the 1st of November …? — C3roe
– C3roe, Commented Mar 23, 2015 at 1:51

Gordon Linoff · Accepted Answer · 2015-03-23 01:44:22Z

1

I am guessing that leading zeros are missing from the format. Then, you should use the right format string:

SELECT STR_TO_DATE(RIGHT(CONCAT('0', colName), 8), '%m%d%Y')

Alternatively, you could construct the date string by pieces:

SELECT DATE(CONCAT_WS('-', right(colName, 4), left(right(colname, 6), 2),
                      left(right(concat('0', colname), 8), 2)
                     )
           )

answered Mar 23, 2015 at 1:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

caro Over a year ago

these are all great solutions but this is the best for what i need, thanks a million

Thiago Elias · Accepted Answer · 2015-03-23 01:52:23Z

0

I don't know if you need to convert directly in the query, but if you want to work with the column in PHP instead of mysql, you may do this in a loop:

<?php
    $matches = array();
    preg_match("%([0-9]{2})([0-9]{1,2})([0-9]{4})%","12292014", $matches);
    $date = $matches[1] . '/' . $matches[2] . '/' . $matches[3];
    var_dump($date);

I used "12292014" but you just need to change this for your iterating value.

(See in action here)

answered Mar 23, 2015 at 1:52

Thiago Elias

9391 gold badge6 silver badges21 bronze badges

Comments

Danijel · Accepted Answer · 2015-03-23 02:46:13Z

0

This could be '%m%e%Y' format ( 'mjY' in PHP ), since leading zeros are missing, php and mysql are having problems to automatically convert this.

php solution ( convert string to 'mj-Y' and use DateTime::createFromFormat ):

$d = '1072015';
$date = DateTime::createFromFormat( 'mj-Y', sprintf( '%s-%s', substr( $d, 0, -4 ), substr( $d, -4 ) ) );
echo $date->format('Y-m-d');

mysql solution ( convert to '%m%e-%Y' ):

STR_TO_DATE( CONCAT( LEFT( column, LENGTH(column) - 4 ), '-', RIGHT( postTitle, 4 ) ), '%m%e-%Y' )

It could also be '%c%e%Y' format, and then things are getting more complicated.

answered Mar 23, 2015 at 2:46

Danijel

12.8k5 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

Date formatting CSV MySQL PHP

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related