CODEWITHSUNDEEP
mysqldate
StatguyUser
StatguyUser

Reputation: 2665

create date in MySql from a varchar column parts

I am new to SQL. I imported multiple CSV file through ETL. there is a date column and some dates are formatted as mm-dd-yyyy and some others as mm/dd/yyyy. I know how to get the individual part of day, month and year as follows.

#month
SELECT mid(My_Date,1,2) as Month_ FROM rl_transactional.mydb;
#day
SELECT mid(My_Date,4,2) as Day_ FROM rl_transactional.mydb;
#year
SELECT mid(My_Date,7,4) as Year_ FROM rl_transactional.mydb;

I want to concatenate all three components to get a date. When i run the query

select CAST(
  (SELECT mid(My_Date,7,4) FROM rl_transactional.mydb) + '-' 
  + (int, SELECT mid(My_Date,1,2) FROM rl_transactional.mydb) + '-' 
  + (int, SELECT mid(My_Date,4,2) FROM rl_transactional.mydb)
 AS DATETIME)

It gives me error 

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'int, SELECT mid(My_Date,1,2) FROM rl_transactional.mydb) + '-' + (int, SELECT mi' at line 1

Thanks!

Upvotes: 0

Views: 209

Answers (3)

Shadow
Shadow

Reputation: 34232

  1. In mysql + operator does not do string concatenation, you have to use concat() function for that or omit the operators entirely.

  2. You are missing the cast function name just from the (int...) sections. But using the cast is superfluous if you convert it back to string anyway.

I woul use the following sql:

SELECT CAST(CONCAT(mid(My_Date,7,4), '-', mid(My_Date,1,2), '-', mid(My_Date,4,2)) AS DATETIME)
FROM rl_transactional.mydb

But you can use str_to_date() function instead the above.

Upvotes: 1

undefined_variable
undefined_variable

Reputation: 6218

select CAST(CONCAT(mid(My_Date,7,4) ,'-' , mid(My_Date,1,2) , '-' ,mid(My_Date,4,2) ) AS DATETIME FROM rl_transactional.mydb

Changes to your above query.

NOTE: I would prefer to use STR_TO_DATE function of MySQL

Upvotes: 1

Alex
Alex

Reputation: 17289

Try this way:

SELECT CAST(CONCAT(MID(My_Date,7,4) , '-' 
  , MID(My_Date,1,2)  , '-' 
  , MID(My_Date,4,2))
 AS DATETIME)
FROM rl_transactional.mydb

Upvotes: 1

Related Questions