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!

Is the database and source of query in the same time zone? if not then kindly set timezone before querying in the date object. Or after querying convert the time to the desired time zone. — Mrunal Gosar, Jun 07 '21 at 14:18

Arvind Kumar Avinash · Accepted Answer · 2021-06-07T14:52:43.560

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.}

@AravindKumarAvinash Thanks for the quick help! How to convert LocalDateTime to java.sql.Date? — veejay, Jun 07 '21 at 14:31
@AravindKumarAvinash - Thanks for the help. I tried the above code. It is actually incrementing the time by 2 hours Input: '2021-05-31T14:12:44.8020000' Output: "2021-05-31 16:26:24.000000" — veejay, Jun 07 '21 at 14:41

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

1 Answers1