How to convert "01/30/2018 02:29:23 PM" into smalldatetime format? I am using MSSQL for my application and the column is smalldatetime format

Question

I am trying to convert a string which is in the format " 01/02/2018 02:48:04 PM " to a smalldatetime format to insert into MS SQL.

import java.sql.Date;
import java.sql.Timestamp;
import java.time.Instant;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeFormatterBuilder;
import java.util.Locale;

public class TimeStampTest {

    public static void main(String[] args) {
        String originalDate ="01/02/2018 02:29:23 PM";
        System.out.println(convertToSmallDatetimeFormat(originalDate));
    }

    private static Timestamp convertToSmallDatetimeFormat(String originalDate) {


        DateTimeFormatter formatter = new DateTimeFormatterBuilder()
                .parseCaseInsensitive()
                .appendPattern("MM/dd/yyyy hh:mm:ss a")
                .toFormatter(Locale.US);
        LocalDateTime localDate = LocalDateTime.parse(originalDate,formatter);

        return new Timestamp(localDate.getNano()/1000000);

    }

}

But I realized that when I do the following code

LocalDateTime localDate = LocalDateTime.parse(originalDate,formatter);

I am getting some garbage value 2018-01-02T15:58:04

which results in localDate.getNano() to be 0

Answer 1

You can actually do it from the MySQL query itself which may save you some time:

Depending on what version you use, this is for MariaDB

SELECT DATE_FORMAT(date, "%Y-%m-%d  %H:%M:%S") date FROM `table`

Answer 2

You can use Timestamp.valueOf

private static Timestamp convertToSmallDatetimeFormat(String originalDate) {
    DateTimeFormatter formatter = new DateTimeFormatterBuilder()
            .parseCaseInsensitive()
            .appendPattern("MM/dd/yyyy hh:mm:ss a")
            .toFormatter(Locale.US);
    LocalDateTime localDate = LocalDateTime.parse(originalDate,formatter);
    System.out.println("****"+localDate);
    return Timestamp.valueOf(localDate);
}

Answer 3

To convert the string and insert it as smalldatetime you don't need to use java.sql.Timestamp at all. Simply pass the LocalDateTime object to PreparedStatement#setObject like this:

Statement st = conn.createStatement();
st.execute("CREATE TABLE #tmp (id int PRIMARY KEY, sdt smalldatetime)");

PreparedStatement ps = conn.prepareStatement("INSERT INTO #tmp (id, sdt) VALUES (?, ?)");
ps.setInt(1, 1);
String originalDate = "01/02/2018 02:29:43 PM";
DateTimeFormatter formatter = new DateTimeFormatterBuilder()
        .parseCaseInsensitive()
        .appendPattern("MM/dd/yyyy hh:mm:ss a")
        .toFormatter(Locale.US);
LocalDateTime ldt = LocalDateTime.parse(originalDate, formatter);
System.out.println(ldt);  // 2018-01-02T14:29:43
ps.setObject(2, ldt);
ps.executeUpdate();

And, if you are using mssql-jdbc version 7.1.0 or later you can retrieve the values directly into a LocalDateTime object (I created the pull request for that feature)

ResultSet rs = st.executeQuery("SELECT sdt FROM #tmp WHERE id=1");
rs.next();
LocalDateTime retrieved = rs.getObject(1, LocalDateTime.class);
System.out.println(retrieved);  // 2018-01-02T14:30
// note that the minutes value is rounded