CODEWITHSUNDEEP
phpmysqldate
caro
caro

Reputation: 892

Date formatting CSV MySQL PHP

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

1152015
12242014
12292014
1072015
1142015
1152015
1142015
1142015
1052015
12232014
1062015
12222014
1052015

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

Upvotes: 0

Views: 530

Answers (3)

Danijel
Danijel

Reputation: 12709

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.

Upvotes: 0

Thiago Elias
Thiago Elias

Reputation: 939

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270833

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)
                     )
           )

Upvotes: 1

Related Questions