How to make SQL query with empty date?

Question

I want to make this query:

    Class.forName("org.postgresql.Driver");
    Connection conn= null;
    conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgis","postgres","123456");
    PreparedStatement ps = null;
    String sql = "SELECT num FROM parcels WHERE code_initial_right =(SELECT code_document FROM documents WHERE number_document=? or date_document='"+docd+"')";
    ps = conn.prepareStatement(sql);
    ps.setString(1,docn);
    ResultSet rs = ps.executeQuery();

But docd can be equals null. When i try this i get an error:

javax.servlet.ServletException: org.postgresql.util.PSQLException: ERROR: invalid input syntax for type date: ""

What can i do with this?

How appending or date_document='"+docd+" to String sql only when is not null? — PbxMan
– PbxMan, Commented Nov 29, 2012 at 9:46
You mean use if in case when docd=null assign docd to randomdate? — Kliver Max
– Kliver Max, Commented Nov 29, 2012 at 9:47

Mahmoud Gamal · Accepted Answer · 2012-11-29 09:42:42Z

3

Use the IS NULL predicate:

....
OR docd IS NULL OR date_document = docd;

answered Nov 29, 2012 at 9:42

Mahmoud Gamal

80.3k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mahmoud Gamal Over a year ago

@KliverMax - Sorry, I write docd with $ to indicate that it is a parameter, you have to write it with the proper way with Java. I edited my answer.

Kliver Max Over a year ago

@ Mahmoud Gamal in answer you mean OR date_document IS NULL OR date_document = docd;? Couse i get error: ERROR: column "docd" does not exist.

Kliver Max Over a year ago

But if i use OR date_document IS NULL OR date_document = docd; i get erorr: ERROR: invalid input syntax for type date: ""

Aleksandr M · Accepted Answer · 2012-11-29 09:54:34Z

1

Using if:

StringBuilder sql = new StringBuilder("SELECT num FROM parcels WHERE code_initial_right =");
if (docd != null) {
   sql.append("(SELECT code_document FROM documents WHERE number_document=? or date_document='"+docd+"')");
}else {
   sql.append("(SELECT code_document FROM documents WHERE number_document=?)");
}
ps = conn.prepareStatement(sql.toString());

answered Nov 29, 2012 at 9:54

Aleksandr M

24.4k13 gold badges75 silver badges148 bronze badges

1 Comment

Kliver Max Over a year ago

Yeah its will 100% works. But i thought that i can find something in SQL or jdbc functions(

Andrey Khmelev · Accepted Answer · 2012-11-29 09:55:45Z

1

try ISNULL(date_document, default_date) like this:

String sql = "SELECT num FROM parcels WHERE code_initial_right =(SELECT code_document FROM documents WHERE number_document=? or isnull(date_document, '"+docd+"') ='"+docd+"')";

answered Nov 29, 2012 at 9:55

Andrey Khmelev

1,1718 silver badges13 bronze badges

2 Comments

Kliver Max Over a year ago

ERROR: function isnull(date, unknown) does not exist

Andrey Khmelev Over a year ago

@KliverMax ok, try this: String sql = "SELECT num FROM parcels WHERE code_initial_right =(SELECT code_document FROM documents WHERE number_document=? or NULLIF(date_document, '"+docd+"') ='"+docd+"')";

Collectives™ on Stack Overflow

How to make SQL query with empty date?

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related