I have a csv file in which the Datetime column is defined in "DD-MM-YYYY HH:MM" format. But the problem is MySQL accepts Datetime type as "YYYY-MM-DD HH:MM" only. I want to convert the format given in csv file to format defined by MySQL but I am not able to do it.I tried to covert the format in excel but first there is no datetime format in excel and if I split the column into two then it changes the structure of my file and thus not able to insert in database.So, the only option is to use Java program and change the format before inserting. Can anyone provide me some help in this regard?
This is my class which is getting called for converting the formats but I do not know what is wrong here and whether it is sufficient or I am missing something.(This class is getting accessed from another program.)
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
public class DateUtil {
// List of all date formats that we want to parse.
// Add your own format here.
private static List<SimpleDateFormat>
dateFormats = new ArrayList<SimpleDateFormat>() {
private static final long serialVersionUID = 1L;
{
// add(new SimpleDateFormat("M/dd/yyyy"));
// add(new SimpleDateFormat("dd.M.yyyy"));
// add(new SimpleDateFormat("M/dd/yyyy hh:mm:ss a"));
// add(new SimpleDateFormat("dd.M.yyyy hh:mm:ss a"));
// add(new SimpleDateFormat("dd.MMM.yyyy"));
add(new SimpleDateFormat("dd-MM-yyyy hh:mm"));
add(new SimpleDateFormat("yyyy-MM-dd hh:mm"));
}
};
/**
* Convert String with various formats into java.util.Date
*
* @param input
* Date as a string
* @return java.util.Date object if input string is parsed
* successfully else returns null
*/
public static Date convertToDate(String input) {
Date date = null;
if(input == null ) {
return null;
}
for (SimpleDateFormat format : dateFormats) {
try {
format.setLenient(false);
date = format.parse(input);
} catch (ParseException e) {
//Shhh.. try other formats
}
if (date != null) {
break;
}
}
return date;
}
}
Stringinto aDate(e.g. usingSimpleDateFormatwith the appropriate format) and then pass theDateas as parameter to the applicable MySQL insert code. The database driver will in turn take theDateand insert it as the appropriateDATETIMEtype in the database. (But if you wanted aString, then you could create one [with the applicable format] from aDateobject.)