How to convert a sequence of characters into a date format to store in the database?

Based on the filename i have to break down the string into a different array of elements and i was able to extract the timestamp from the file naming convention and now i need to pass this string to a date format to be stored into the db tables:

Example:
20200913204839 string needs to be converted into date format as 13-sep-2020 20:48:39 (column_name) and the pl/sql block has the parameter value in this format column name-datatype (varchar2) -and value inside the insert statement as :

to_date(column_name,'dd-mon-yyyy hh24:mi:ss')

This is the equivalent matlab code:

 fn.fntestdate = datestr(datenum(column_name,'yymmddHHMM'),'dd-mmm-yyyy HH:MM:SS')

How do i incorporate the same logic in Java?

I tried several functions SimpleDateFormat trying to parse it,but was receiving "unparseable funcction" ERROR.

I think column_name has to be converted into number and then converted to date before parsing it?
Requires several multiple implicit conversions before loading. Suggest the best possible approach for this

Here's an example that works with your test cases:

import java.text.SimpleDateFormat;  
import java.util.Date;  
public class StringToDateExample1 {  
    public static void main(String[] args)throws Exception {  
        String sDate1="20200913204839";  
        Date date1=new SimpleDateFormat("yyyyMMddhhmmss").parse(sDate1);  
        System.out.println(date1);
    }  
}  

With test case 20200913204839 it returns:

Sun Sep 13 20:48:39 UTC 2020

Here's what the characters in the SimpleDateFormat mean:

yyyy signifies a year (Example: 2020)

MM signifies a month (Example: July, August)

dd signifies a day in a month (Example: 15)

HH signifies an hour in a day (Example: 20)

mm and ss signify minutes and seconds respectively (Example: 52:34)

It doesn't have to be a Date - just a String works fine.

And yes - using SimpleDateFormat works just fine.

EDIT: Specific Formatting

I've formatted it specifically for your case:

import java.text.SimpleDateFormat;  
import java.util.Date;  
public class StringToDateExample1 {  
    public static void main(String[] args)throws Exception {  
        String sDate1="20200913204839";  
        Date date1=new SimpleDateFormat("yyyyMMddhhmmss").parse(sDate1);
        String formatted = new SimpleDateFormat("dd-EE-yyyy hh:mm:ss").format(date1);
        System.out.println(formatted);
    }  
} 

We first parse the String into a date, from which we can format.

A test case 20200913204839 returns:

13-Sun-2020 08:48:39

> … Suggest the best possible approach for this

This is exactly what I am doing.

java.time and JDBC 4.2

13-sep-2020 20:48:39 is a nice date and time of day. I suppose that you meant it to indicate a point in time. It only makes sense as such within a time zone. So to store a point in time store the time with a time zone (such as Asia/Ust-Nera) or an offset from UTC (such as +10:00). Since you are using an Oracle database: If I have been correctly informed, the timestamp with time zone datatype of Oracle (contrary to other RDBMSs) is what it says: a timestamp with a time zone. So you should be able to transfer a Java ZonedDateTime to it. Do that.

First, to parse the string:

	DateTimeFormatter formatter = DateTimeFormatter.ofPattern("uuuuMMddHHmmss");
	ZoneId zone = ZoneId.systemDefault();
	
	String dateTimeFromFileName = "20200913204839";
	
	ZonedDateTime dateTime = LocalDateTime.parse(dateTimeFromFileName, formatter)
			.atZone(zone);
	System.out.println("Parsed date-time: " + dateTime);

Output so far is in my time zone:

> Parsed date-time: 2020-09-13T20:48:39+02:00[Europe/Copenhagen]

I have used the default time zone of my JVM. If you can, specify a time zone in the region/city format, for example:

	ZoneId zone = ZoneId.of("Asia/Ust-Nera");

Second, to pass to a PL/SQL block that expects timestamp with time zone (not tested since I haven’t got an Oracle database at hand):

	CallableStatement stmt = yourDatabaseConnection
			.prepareCall("/* Your PL/SQL call goes here */");
	stmt.setObject(1, dateTime);
	stmt.execute();

To sum up the recommendations

• Don’t store nor handle your date as varchar2 in the database but use its datetime or even better timestamp with time zone datatype.
• Don’t send the date as a string to the database. Since JDBC 4.2, send an appropriate type from java.time. The documentation of your JDBC driver should tell you the correct Java type to use.
• In Java always use java.time, the modern Java date and time API, for you date and time work.

Tutorial link

Oracle tutorial: Date Time explaining how to use java.time.