Query a Table based on the column as datetime2(7) from JPA

Question

I need to query list of records between the given date range based on the column which was defined as datetime2(7) in MS SQL Server.

From the HTTP request I will be receiving startDate and endDate as below.

http://{host:port}/api/records/date?startDate=**2021-05-31T14:12:44.8020000**&endDate=**2021-05-31T14:12:44.8020000**

In the database, value in lastUpdated column is stored as 2021-05-31 14:12:44.8020000

I am trying to convert the incoming query params which is a String to java.sql.Date in the code as below

@Override
public Page<Entity> getAllRecordsWithDateRange(String startDate, String endDate) {
   
    Page<Entity> recordsWithinDateRange = null;
    String time = startDate;
    String formatIn = "yyyy-MM-dd'T'HH:mm:ss.SSSSSS";
    String formatOut = "yyyy-MM-dd HH:mm:ss.SSSSSS";
    SimpleDateFormat in = new SimpleDateFormat(formatIn);
    SimpleDateFormat out = new SimpleDateFormat(formatOut);

    Date dateIn = null;
    try {
        dateIn = in.parse(time);
    } catch (ParseException e) {
        e.printStackTrace();
    }
    String valueOut = out.format(dateIn);

    System.out.println(">>> " + valueOut);
    Pageable page = PageRequest.of(0,5000);
    Date date1= null;
    try {
        date1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSSSS").parse(valueOut);
        java.util.Date utilDate = date1;
        recordsWithinDateRange =  repo.getAllRecordsBetweenDates(utilDate,utilDate,page);

    } catch (ParseException e) {
        e.printStackTrace();
    }

    
    return recordsWithinDateRange;
}

Issue I am seeing here is my actual input date is 2021-05-31T14:12:44.8020000 But, after the conversion it is incremented to a different time 2021-05-31 16:26:24.000000. So, query is returning no records from the DB.

Could someone help me to solve this issue? TIA!

Answer 1

The java.util Date-Time API and their formatting API, SimpleDateFormat are outdated and error-prone. It is recommended to stop using them completely and switch to the modern Date-Time API^*.

Solution using java.time, the modern Date-Time API^*:

datetime2 maps to TIMESTAMP ANSI SQL type or LocalDateTime in JDBC.

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        DateTimeFormatter dtfInput = DateTimeFormatter.ofPattern("u-M-d'T'H:m:s.SSSSSSS", Locale.ENGLISH);
        DateTimeFormatter dtfOutput = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSSSSSS", Locale.ENGLISH);
        String strDateTime = "2021-05-31T14:12:44.8020000";
        LocalDateTime ldt = LocalDateTime.parse(strDateTime, dtfInput);
        System.out.println(ldt.format(dtfOutput));
    }
}

Output:

2021-05-31 14:12:44.8020000

ONLINE DEMO

Check this answer to learn how to perform JDBC operations using LocalDateTime.

Solution using legacy API:

SimpleDateFormat does not handle fraction-of-second beyond millisecond precision correctly. Check this answer to learn more about it.

You need to truncate the date-time string to millisecond precision.

import java.text.ParseException;
import java.text.SimpleDateFormat;

public class Main {
    public static void main(String[] args) throws ParseException {
        String strDateTime = "2021-05-31T14:12:44.8020000";
        strDateTime = strDateTime.substring(0, strDateTime.indexOf('.') + 4);

        String formatIn = "yyyy-MM-dd'T'HH:mm:ss.SSS";
        String formatOut = "yyyy-MM-dd HH:mm:ss.SSS";

        SimpleDateFormat in = new SimpleDateFormat(formatIn);
        SimpleDateFormat out = new SimpleDateFormat(formatOut);

        System.out.println(out.format(in.parse(strDateTime)));
    }
}

Output:

2021-05-31 14:12:44.802

ONLINE DEMO

---

^{* For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.}