9 Replies Latest reply: Apr 27, 2007 1:47 PM by 807606 RSS

    Java Dates + Database Weird

    807606
      How come my Java dates from a web form are sometimes being stored in the MSSQL database in two different formats?

      4/26/2007
      and 4/26/2007 12:00:00 PM
        • 1. Re: Java Dates + Database Weird
          807606
          Are the column types DATE and DATETIME, respectively?
          • 2. Re: Java Dates + Database Weird
            807606
            The column is datetime, its one column and I get both the styles of date in it.
            • 3. are u formatting
              807606
              are u formatting the date in java or just creates the date object?
              I suppose you use sql date object, right?
              • 4. Re: are u formatting
                807606
                Here's my few code snippets.
                import java.sql.Date;
                
                /*
                */
                
                Date cdate = new Date(new java.util.Date().getTime());
                         Connection con = null;
                /*
                */
                
                con = ConnectionFactoryWrapper.getConnection("sqlServer");
                            ps = con.prepareStatement(insert);
                            ps.setInt(1, id);
                            ps.setInt(2, rating);
                            ps.setString(3, comments);
                            ps.setTime(4, cdate.getTime());
                            ps.setString(5, completed);
                            ps.execute();
                
                /*
                */
                • 5. Re: are u formatting
                  807606
                  Yup. Those are the next questions:

                  Show me the code:
                  1. How do you insert dates?
                  2. How do you retrieve dates?
                  3. How do you format dates for display?
                  • 6. Re: are u formatting
                    807606
                    ps.setTime(4, cdate.getTime());
                    ??? You've never stated what you want in that column -- the date, the date&time, or just the time?
                    • 7. Re: are u formatting
                      807606
                      Date and time is preferred. Right now it randomly does both so when I run a query on the data it doesn't make it easy to grab it all.
                      • 8. Re: are u formatting
                        807606
                        Date and time is preferred.
                        In that case, I would use java.sql.Timestamp and PreparedStatement method setTimestamp. A java.sql.Date is supposed to be normalized to have hours, minutes, seconds and millis all set to 0.
                        • 9. Re: this could work
                          807606
                          The problem cold be this
                           ps.setTime(4, cdate.getTime());
                          remember, getTome() returns the date in miliseconds, my solutions is:

                          you insert statement cold look like "insert into table vales (CAST('?' AS datetime))"

                          and add this to your code
                          GregorianCalendar gc = new GregorianCalendar();
                                  
                                  gc.setTime(cdate.getTime());
                                  
                                  int year  = gc.get( GregorianCalendar.YEAR );
                                  int month = gc.get( GregorianCalendar.MONTH )+1;
                                  int day   = gc.get( GregorianCalendar.DAY_OF_MONTH );
                                  
                                  String cDate = month+"/"+day+"/"year;
                                  
                                  //instead of 
                                  //ps.setTime(4, cdate.getTime());
                                  //use
                                  ps.setString(4, cDate);