CODEWITHSUNDEEP
javasqlpostgresql
Sajeev
Sajeev

Reputation: 799

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

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.

Upvotes: 1

Views: 117

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

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.

Upvotes: 0

srigin
srigin

Reputation: 106

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

Upvotes: 2

shazin
shazin

Reputation: 21883

Did you try 

rs.getInt("DaysInMonth")

Instead of 

rs.getString("DaysInMonth")

As you are expecting an Integer.

Upvotes: 0

Related Questions