Date value 0000-00-00 cannot be used in java Date

Asked

Viewed 689 times

1

First of all yes, I’ve already added ?zeroDateTimeBehavi‌​or=convertToNullin the connection URL the problem is that in the query the reset date is not null and always release the following Exception:

java.sql.SQLException: Value '0000-00-00' can not be represented as java.sql.Date

I have debugged and always to the same reset date being called by Resultset

regC100.setDtDoc(resultSet.getDate(14));
java database date date jdbc

  • It is not a valid date anyway. Why you do not save NULL in these cases?

  • The smallest possible date in the java date API of the java package java.util or java.sql is 01/01/1970. So, this date is non-existent for the api.

  • Because queries(iinserts) are generated dynamically then making this treatment would be quite complicated

2 answers

1

The other fields were omitted but I managed to get all calling them individually with the ALIASthe way I managed to solve it was like this:

SELECT if(DT_DOC='0000-00-00',NULL,DT_DOC) as DT_DOC FROM reg_c100;

0

In Mysql 0000-00-00 is considered a valid date, but cannot be represented as java.sql.Date.

You can use a query that returns NULL in the event of a date 0000-00-00, or the actual value otherwise:

SELECT
  CASE WHEN `data`!='0000-00-00' THEN `data` END date
FROM
  suaTabela

  • I’m gonna do a test here

  • I did a test using your example querie and it was like this: SELECT c100.*,CASE WHEN DT_DOC='0000-00-00' THEN DT_DOC=null END date FROM reg_c100 as c100 but the dates returned are not null can you tell me what is wrong? I thank you already.

  • Try the following: SELECT CASE WHEN DT_DOC != '0000-00-00' THEN DT_DOC END NULL FROM reg_c100

  • 1

    Basically you select only the date, but if it is different from the standard 0000-00-00 it returns the date itself validaotherwise it will return null ai depending on your problem you can add the Where to other filters

  • I could not run your query for some reason phpmyadmin showed a syntax error, I believe it was in the part of NULL, but I managed to fix the error I will show the correct answer, but yours helped

  • CASE is not IF, is more like a ternary operator ?:. So you can’t say THEN DT_DOC = NULL, have to say THEN NULL ELSE DT_DOC. Essentially the CASE works like this: CASE WHEN [teste1] THEN [valor1] WHEN [teste2] THEN [valor2] .... ELSE [valorn] END; the SQL tries [teste1], [teste2], etc. in order and returns the [valori] corresponding to the first [testei] that gives true.

Show 1 more comment

Browser other questions tagged java database date date jdbc

You are not signed in. Login or sign up in order to post.