java - Exception MysqlDataTruncation when parsing a String 1899-12-30 - Stack Overflow

admin管理员组

文章数量:1434184

public Timestamp convertFromDate(String fromDate) throws ParseException {
        String[] possibleFormats = { "dd/MM/yyyy", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd" };
        ParseException lastException = null;

        for (String format : possibleFormats) {
            try {
                SimpleDateFormat dateFormat = new SimpleDateFormat(format);
                dateFormat.setLenient(false);
                Date parsedDate = dateFormat.parse(fromDate);
                return new Timestamp(parsedDate.getTime());
            } catch (ParseException e) {
                lastException = e;
            }
        }
        throw lastException;
    }

This is the code which i am using to parse a string in possible Timestamp format but when it pass through each it gets parsed but generates a Timestamp for example for a string 1899-12-30 to 1899-12-30 00:00:00.0 this .0 is causing an error. Which format or what change do i make to this code to make it work?

Basically I am concerned about this parsing. The other logic for saving is done by calling Spring Boot respository where the parsed date is set to the object and then the repository save method is called, from where the exception is generated. I know this is the method causing me the error which is generating the wrong timestamp format.

public Timestamp convertFromDate(String fromDate) throws ParseException {
        String[] possibleFormats = { "dd/MM/yyyy", "yyyy-MM-dd HH:mm:ss", "yyyy-MM-dd" };
        ParseException lastException = null;

        for (String format : possibleFormats) {
            try {
                SimpleDateFormat dateFormat = new SimpleDateFormat(format);
                dateFormat.setLenient(false);
                Date parsedDate = dateFormat.parse(fromDate);
                return new Timestamp(parsedDate.getTime());
            } catch (ParseException e) {
                lastException = e;
            }
        }
        throw lastException;
    }

This is the code which i am using to parse a string in possible Timestamp format but when it pass through each it gets parsed but generates a Timestamp for example for a string 1899-12-30 to 1899-12-30 00:00:00.0 this .0 is causing an error. Which format or what change do i make to this code to make it work?

Basically I am concerned about this parsing. The other logic for saving is done by calling Spring Boot respository where the parsed date is set to the object and then the repository save method is called, from where the exception is generated. I know this is the method causing me the error which is generating the wrong timestamp format.

• java
• mysql
• spring-boot
• jpa
• timestamp

Share Improve this question Follow edited Nov 19, 2024 at 6:10 Anonymous 86.6k1515 gold badges163163 silver badges178178 bronze badges asked Nov 18, 2024 at 14:10 Ibrahim AlviIbrahim Alvi 2111 silver badge44 bronze badges 19

• 1 Your code works just fine for me. yyyy-MM-dd HH:mm:ss doesn't throw any exception when the string 1899-12-30 00:00:00.0 is supplied to the method. However, if your string contains milliseconds, and you wanna make sure to parse them, then you need to include the millisecond group as well SSS docs.oracle/javase/8/docs/api/index.html?java/text/… Also, I don't know the scope of your program, but you might wanna take a look to the java.time library docs.oracle/javase/10/docs/api/java/time/… – dani-vta Commented Nov 18, 2024 at 14:22
• 4 Do you have to use Timestamp, Date and SimpleDateFormat? They are considered notoriously troublesome and de facto outdated. You could use java.time instead… – deHaar Commented Nov 18, 2024 at 14:35
• 2 but be aware that using S or SSS (@dani-vta) means milliseconds and 1899-12-30 00:00:00.1 would mean 1 millisecond after midnight (0.001) and not 100 milliseconds (0.100) - one more reason to stop using these outdated classes, replaced by classes in the java.time package in Java 8 (10 years ago) – user85421 Commented Nov 18, 2024 at 14:49
• 1 You want something much more like: DateTimeFormatter dtf = new DateTimeFormatterBuilder().optionalStart().appendPattern("dd/MM/yyyy").optionalEnd().optionalStart().appendPattern("yyyy-MM-dd HH:mm:ss").optionalEnd().parseDefaulting(ChronoField.HOUR_OF_DAY, 0).optionalStart().appendPattern("yyyy-MM-dd").optionalEnd().parseDefaulting(ChronoField.MINUTE_OF_HOUR, 0).parseDefaulting(ChronoField.SECOND_OF_MINUTE, 0).toFormatter(); LocalDateTime ldt = LocalDateTime.parse(input, dtf); – g00se Commented Nov 18, 2024 at 15:32
• 1 What exactly is the data type of your column in the database? What is your database engine? Are you trying to record a moment, a specific point on the timeline? – Basil Bourque Commented Nov 18, 2024 at 22:06

 |  Show 14 more comments

1 Answer 1

Sorted by: Reset to default 3

You Question makes no sense. Your title claims a MysqlDataTruncation exception, but your code does not involve MySQL nor JDBC. I can respond in part, specifically to your code snippet.

Regarding the code snippet, you have two major problems:

• You ignoring crucial issue of time zone. Your results will be inconsistent, varying by the JVM’s current default time zone.
• You are using terribly-flawed date time classes that are now legacy, having been supplanted long ago by the java.time classes defined in JSR 310.

JDBC 4.2 and later requires every JDBC driver to support the java.time types. Never use the classes Timestamp, Date, Calendar, SimpleDateFormat, etc. Use only java.time.

Regarding parsing, I would first check the length of string.

• 10 characters means one of our two date-only formats.
• 19 characters means a date-with-time format.
• Any other length means invalid input.

For the 10-character length, check for the presence of slash or hyphen character to parse as a LocalDate. For the slashes, define a formatting pattern with DateTimeFormatter class. Your other format complies with ISO 8601 standard. The java.time classes by default use the standard formats. So no need to define a formatting pattern, just parse directly.

For the 19-character length, swap the SPACE character in the middle for a T to comply with the ISO 8601 standard. Then parse as a LocalDateTime object.

For our date-only, I assume you want to default to the time 00:00 as we derive a LocalDateTime object from our LocalDate object.

But neither a LocalDate nor LocalDateTime object represents a moment. I assume you want a moment, a point on the timeline, though you did not specify one way or the other.

For a moment, we need a third part. We need the context of a time zone or offset-from-UTC to determine a moment. I will assume you meant the offset of zero hours-minutes-seconds from UTC. This offset is represented by the constant ZoneOffset.UTC.

We can apply ZoneOffset.UTC to our LocalDateTime object to generate a OffsetDateTime object. At this point we are ready to submit this value to a SQL database column of a type akin to the SQL standard type of TIMESTAMP WITH TIME ZONE.

If you don’t want the fraction of a second, truncate.

String input = "23/01/2025";

// Represent a moment in `OffsetDateTime` for recording in a SQL database column of
// a type akin to the SQL standard type `TIMESTAMP WITH TIME ZONE`.
OffsetDateTime odt = null;
// We expect only 3 possible input string formats:
// Date-only;  "YYYY-MM-DD" or "DD/MM/YYYY".
// Date-with-Time:  "YYYY-MM-DD HH:MM:SS".  (lacks offset or zone)
long countCodePoints = input.codePoints ( ).count ( );
if ( countCodePoints == 10 )
{
    DateTimeFormatter f =
            input.contains ( "/" )
                    ? DateTimeFormatter.ofPattern ( "dd/MM/uuuu" )
                    : DateTimeFormatter.ISO_LOCAL_DATE;
    LocalDate ld = LocalDate.parse ( input , f );
    odt = ld.atStartOfDay ( ).atOffset ( ZoneOffset.UTC );
}
else if ( countCodePoints == 19 )
{
    LocalDateTime ldt = LocalDateTime.parse ( input.replace ( " " , "T" ) );
    odt = ldt.atOffset ( ZoneOffset.UTC );
}
else
{
    throw new IllegalArgumentException ( "Invalid date string submitted. Must be either 10 or 19 characters." );
}
odt = odt.truncatedTo( ChronoUnit.SECONDS ) ;
// myPreparedStatement.setObject( … , odt ) ;

All of this has been discussed many times on Stack Overflow. Search to learn more.

本文标签： javaException MysqlDataTruncation when parsing a String 18991230Stack Overflow