SQL query showing different answers while executing in SQL Editor and through Java

Question

I need to get the number of days in a month from the Register table. Im using the following SQL Query :

SELECT DateTo::Date-DateFrom::Date AS DaysInMonth FROM Register;

While executing it in SQL Editor of PostgreSQL Im getting the following result : enter image description here

While executing the same through java code Im getting the result as 29 days instead of 29 Im adding the java code here

sql = " SELECT DateTo::Date-DateFrom::Date AS DaysInMonth FROM Register; "                  
    PreparedStatement pstmt = null;
    ResultSet rs = null;                
    try {
        pstmt = DB.prepareStatement(sql, null);
        pstmt.setInt(1, payReg.getC_Period_ID());
        rs = pstmt.executeQuery();                      
        while (rs.next()){                          
            System.out.println(rs.getString("DaysInMonth"));
        }
    }catch (SQLException e){
        e.printStackTrace();
    }

What may be the reason..? I need to get just 29 from the java execution. How do I get it..? Thanks in advance.

For calculating days between two timestamps, consider this closely related question - which is more sophisticated than to get the number of days in a month ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 8, 2013 at 15:47

srigin · Accepted Answer · 2013-10-08 08:13:03Z

2

Try this.. DATE_PART('days',DATE_TRUNC('month', DateTo) - DATE_TRUNC('month', Datefrom)) as DaysInMonth

edited Oct 8, 2013 at 8:13

answered Oct 8, 2013 at 7:42

srigin

1068 bronze badges

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

1 Comment

Sajeev Over a year ago

Thank you Sreekuttan. Its working properly, but what may be the problem while executing it through java.?

shazin · Accepted Answer · 2013-10-08 07:06:35Z

0

Did you try

rs.getInt("DaysInMonth")

Instead of

rs.getString("DaysInMonth")

As you are expecting an Integer.

answered Oct 8, 2013 at 7:06

shazin

22k3 gold badges58 silver badges72 bronze badges

1 Comment

Sajeev Over a year ago

It will throw an Exception saying "bad value for int : 29 days"

Erwin Brandstetter · Accepted Answer · 2013-10-08 15:33:50Z

0

Postgres certainly returns an integer when subtracting two dates. It looks like the cast did not happen. I suspect that :: in the PostgreSQL specific syntax DateTo::Date has special meaning in your client. Try the SQL standard syntax for casting:

Also demonstrating better ways to ...

... get the number of days in a month

EXTRACT(day FROM cast(DATE_TRUNC('month'
                                  ,now() + interval '1 month') AS date) - 1);

Better yet, use this simpler and faster expression that doesn't need a cast at all:

EXTRACT(day FROM DATE_TRUNC('month', now()) + interval '1 mon - 1 day');

Set log_statement = ALL temporarily and check in the Postgres DB logs what the server actually gets.

edited Oct 8, 2013 at 15:33

answered Oct 8, 2013 at 7:51

Erwin Brandstetter

668k160 gold badges1.2k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

SQL query showing different answers while executing in SQL Editor and through Java

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related