?
Solved

Date Related Problem

Posted on 2003-03-16
18
Medium Priority
?
182 Views
Last Modified: 2010-04-01
I am doing something like date calculation statement. I have some question need to ask.

This is my MSSql data look like

ItemCode        Date
ABC123          2/26/2003 6:31:00 PM
.                .
.                .
.                .

And when I retrieve this data,

I see this

ItemCode       Date
ABC123          

Fine, this output data look fine for me. But when I want to do some sorting on this data (from MSSQL), I add in something in my SQL Statement, like this,

SELECT * FROM table_name WHERE StaffID='SKL' and Date>='2002-01-01'

But end up, it's still list out all the data. Meaning it's doesn't care about the condition that i set Date>='2002-01-02'.

Can anyone tell me how can I set the control in the date side? Let say I only interest on the data started from 2002 January 01??
Thank you.
0
Comment
Question by:superman19
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
18 Comments
 

Author Comment

by:superman19
ID: 8149701
sorry, my JSP page display this

ItemCode     Date
ABC123      2002-07-12

And no this

ItemCode     Date
ABC123    
0
 
LVL 92

Expert Comment

by:objects
ID: 8149777
Try using a PreparedStaement and placeholders:

DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
java.sql.Date date = new java.sql.Date(df.parse("2002-01-01").getTime());
PreparedStatement s = connection.prepareStatement("SELECT * FROM table_name WHERE StaffID=? and Date>=?");
s.setString(1, "SKL");
s.setDate(2, date);
ResultSet rs = s.executeQuery();

0
 
LVL 1

Expert Comment

by:umangjoshi
ID: 8151168
you can not compare date as u mention in query in MySQL

use this
To_Days(Date) >= To_Days('2002-01-01')
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:superman19
ID: 8158263
Guys, I am referring to the calculation part of the date.
Let's say for example, I need to calculate how many day in between 2/2/2002 and 3/3/2003.

How??
0
 
LVL 92

Accepted Solution

by:
objects earned 200 total points
ID: 8158294
Depends on how you define number of days.

Either use the Calendar class, or calculate the number of milliseconds between the two dates and divide by (1000*60*60*24).
0
 
LVL 92

Expert Comment

by:objects
ID: 8158303
int days = (d2.getTime() - d1.getTime())/(1000*60*60*24);
0
 

Author Comment

by:superman19
ID: 8172622
objects, I have a strange problems. (Let's ignore the above problem first).

My date in the MS SQL is stated as smalldatetime. when I am trying to pull data from that field, it's cause me problem.

db_date=rs2.getString("DateCreated");

This line of code will never run!! I declared the db_date as String, and end of the javabean, I also declare the method to return a String value.

And I also tried to user java,util.Date for db_date and pull rs2.getDate(""), also give me the same problem.

Cna help??
0
 
LVL 92

Expert Comment

by:objects
ID: 8176935
try:

java.sql.Date db_date = rs2.getDate("DateCreated");
0
 

Author Comment

by:superman19
ID: 8178211
object, negative....

this is my latest code,

java.sql.Date db_date;

   db_date=rs2.getDate("DateCreated");

public java.sql.Date getDb_date() {
    return db_date;
}

..
..

* my DateCreated is in smalldatetime....

How??
0
 
LVL 92

Expert Comment

by:objects
ID: 8178253
> negative....

More information please :)
Any errors etc.
0
 

Author Comment

by:superman19
ID: 8178618
object,
I couldn't retrieve the DateCreated from my MSSQL which datatype is smalldatetime.

in the block of retriving info,

cust_name=rs2.getString("Cust");
System.out.println(cust_name);
address=rs2.rs2.getString("Addr);
System.out.println(address);
db_date=rs2.getData("DateCreate");
System.out.println(db_date);

my db_date=rs2.getDate("DateCreated"), didn't execute,
it's totally stop at the line before that.

I have put al sort of trace print, within  the method, within the JSP called tag, it's all printed out except this line.
db_date=rs2.getData("DateCreate");

And for my tomcat console, I have see no errors....
:-(
No exception... at all.
see the below code,

        public boolean hasNext() {
            boolean hasElement = false;
                  System.out.println("Inside the Loop ");
            try {
                 if (rs2.next()) {
               System.out.println("Next() return " + rs2.next() );
                              customer_name=rs2.getString("CustomerName");
                       System.out.println("Inside the Loop " + customer_name);
               db_date=rs2.getDate("DateCreated");
                       System.out.println("Inside the Loop " + db_date);
                             
                hasElement = true;
               System.out.println("HasElement "+hasElement);
               
          } else {
                hasElement      =false;              
                 }
            } catch(SQLException e) { }
            return hasElement;
        }


And the output is :
"Inside the Loop "
Next() return true;
System.out.println("Inside the Loop " + customer_name);

But the output after      System.out.println("Inside the Loop " + db_date);
          System.out.println("HasElement "+hasElement);

didn't pritn out.
         
0
 

Author Comment

by:superman19
ID: 8178619
object,
I couldn't retrieve the DateCreated from my MSSQL which datatype is smalldatetime.

in the block of retriving info,

cust_name=rs2.getString("Cust");
System.out.println(cust_name);
address=rs2.rs2.getString("Addr);
System.out.println(address);
db_date=rs2.getData("DateCreate");
System.out.println(db_date);

my db_date=rs2.getDate("DateCreated"), didn't execute,
it's totally stop at the line before that.

I have put al sort of trace print, within  the method, within the JSP called tag, it's all printed out except this line.
db_date=rs2.getData("DateCreate");

And for my tomcat console, I have see no errors....
:-(
No exception... at all.
see the below code,

        public boolean hasNext() {
            boolean hasElement = false;
                  System.out.println("Inside the Loop ");
            try {
                 if (rs2.next()) {
               System.out.println("Next() return " + rs2.next() );
                              customer_name=rs2.getString("CustomerName");
                       System.out.println("Inside the Loop " + customer_name);
               db_date=rs2.getDate("DateCreated");
                       System.out.println("Inside the Loop " + db_date);
                             
                hasElement = true;
               System.out.println("HasElement "+hasElement);
               
          } else {
                hasElement      =false;              
                 }
            } catch(SQLException e) { }
            return hasElement;
        }


And the output is :
"Inside the Loop "
Next() return true;
System.out.println("Inside the Loop " + customer_name);

But the output after      System.out.println("Inside the Loop " + db_date);
          System.out.println("HasElement "+hasElement);

didn't pritn out.
         
0
 
LVL 92

Expert Comment

by:objects
ID: 8178750
> System.out.println("Next() return " + rs2.next() );

Firstly, get rid of that statement, or at least the call to rs2.next(). This will jump to to the next row in the rs which is not I think what you want to do.

> } catch(SQLException e) { }

And if you don't print out the exception, how do you know if you are getting one or not :)

0
 

Author Comment

by:superman19
ID: 8179686
OK Ok Ok ...

so sorryyyyyyyy

doing that
0
 
LVL 92

Expert Comment

by:objects
ID: 8184356
Thats ok :) Have done the same thing myself.

And if you still have problems with getDate() then give getObject() a try.
0
 

Author Comment

by:superman19
ID: 8199115
object, sorry fo rthe late reply. LAst few days in MC.

I have added the e.printStackTrace() in the SQL exception cathc method, yet, I couldn't see the error log is in where.

I am using apache tomcat v4.1.


                hasElement      =false;              
                 }
            } catch(SQLException e) { e.printStackTrace(); }
            return hasElement;  
0
 
LVL 92

Expert Comment

by:objects
ID: 8199184
that won't go to the error log, it'll go to the console.
Which I think gets logged to catalina.out.
0
 
LVL 92

Expert Comment

by:objects
ID: 8836433
:-)

http://www.evalu8.com.au
"The Essential Feedback Tool"
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article lists the top 5 free OST to PST Converter Tools. These tools save a lot of time for users when they want to convert OST to PST after their exchange server is no longer available or some other critical issue with exchange server or impor…
In this modest contribution, I want to share with the IT community (especially system administrators, IT Support Engineers and IT Help Desks) about Windows crashes/hangs and how to deal with these particular problems.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question