CODEWITHSUNDEEP
phpmysqlsql
dallas
dallas

Reputation: 101

Converting Mysql text to date

I currently am working with a database that has a month column and a year column.. Both are of type text. Month is stored as 'January' and year is stored as you would expect, '2016'..

Any recommendations for concatenating these and converting them to a date type?

Upvotes: 0

Views: 48

Answers (3)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use a query like this. you only must change the strings to your fieldnames:

sample

select STR_TO_DATE(concat('2016',' ','April','1'), '%Y %M %D');

result

2016-04-01

Upvotes: 1

SeanBartram
SeanBartram

Reputation: 45

This works for me:

select convert(date,(concat('January',' ','2016')))

Upvotes: 0

phpFreak
phpFreak

Reputation: 143

    $months = [
    'january'   => 1,
    'february'  => 2,
    'march'     => 3,
    'april'     => 4,
    'may'       => 5,
    'june'      => 6,
    'july'      => 7,
    'august'    => 8,
    'september' => 9,
    'october'   =>10,
    'november'  =>11,
    'december'  =>12
    ];

    $year       = 2016;
    $month_text = 'january';
    $day        = 1;

    if($months[strtolower($month_text)]<10){
       $month = '0'.$months[strtolower($month_text)];
    }else{
       $month = $months[strtolower($month_text)];
    }
    if($day<10){
       $day = '0'.$day;
    }else{
       $day = $day;
     }


     echo $year.'-'.$month.'-'.$day;

create a new feld in db with type date and insert it into the db. maybe wrap this in a function and put it in a while loop..?

Upvotes: 0

Related Questions