java Date problem

[Malli Raman]

Hi,

I don't know why java returns SELECT to_char(SYSDATE,'d') from dual returns 5 instead of 4 (SQL). Please help me to sort out this problem.

Thanks & Regards,
M.S.Raman


The below program return following output:
2004-09-02 11:28:48.0
5

public static void main(String[] args) throws Exception {

String queryString1 = "SELECT SYSDATE FROM dual";
String queryString2 = "SELECT to_char(SYSDATE,'d') FROM dual";
JDBCHelper jdbcHelper = new JDBCHelper();
Connection connection = jdbcHelper.getConnection();
Statement statementObject1 = connection.createStatement();
Statement statementObject2 = connection.createStatement();
ResultSet rs1 = statementObject1.executeQuery(queryString1);
ResultSet rs2 = statementObject2.executeQuery(queryString2);

String resultString1 = null;
String resultString2 = null;
if(rs1.next())
resultString1 = rs1.getString(1);

if(rs2.next())
resultString2 = rs2.getString(1);

System.out.println(resultString1);
System.out.println(resultString2);
}

[Malli Raman]

Hi,

I have found that to_char(DATE,'d') returns 1 for sunday and 2 for Monday and so on.

But I don't understand why there is no uniformity across other java api methods. In other methods it has been assumed that sunday as 0 and Monday as 1 and so on.

Please let me know if I am correct?

Regards,
M.S.Raman

[Brian Tinnel]

Remember that the SQL statement is executed by the database. So when to_char(DATE, 'd') is processed by the database, it returns a "5". JDBC just sees that it is getting a String and returns it to your program.

[Malli Raman]

Originally posted by Brian Tinnel:
Remember that the SQL statement is executed by the database. So when to_char(DATE, 'd') is processed by the database, it returns a "5". JDBC just sees that it is getting a String and returns it to your program.

Hi,

In the above case SQL Prompt returns only 4 for thursday but Java returns 5 for thursday!!! for this method. But in Java api it has mentioned that 4 will represent for Thursday. why such a difference. Is it a bug?

Regards,
M.S.Raman

[Brian Tinnel]

Maybe there is a difference in configurations between SQL Prompt and the java driver. Most likely the default Locale for SQL Prompt sets up the days of the week as Sun=0, Mon=1, Tue=2, etc. But the Locale setting being used by the JDBC driver is using Sat=0, Sun=1, Mon=2, etc. If so, there might be a property for the jdbc driver you are using to set the proper locale in your app server to be the same as the one being used by SQL Prompt.

[Malli Raman]

Originally posted by Brian Tinnel:
Maybe there is a difference in configurations between SQL Prompt and the java driver. Most likely the default Locale for SQL Prompt sets up the days of the week as Sun=0, Mon=1, Tue=2, etc. But the Locale setting being used by the JDBC driver is using Sat=0, Sun=1, Mon=2, etc. If so, there might be a property for the jdbc driver you are using to set the proper locale in your app server to be the same as the one being used by SQL Prompt.

Thanks Brian. I have already fixed this bug in my application.
Regards,
M.S.Raman