#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    New Jersey
    Posts
    100
    Rep Power
    13

    Cool Java: Formatting Dates for SQL Inserts or Updates


    How can I format a date and insert it into a database as a date/time data type? The database I am currently using is MS Access and the date should be in the format mm/dd/yy

    In my JSP code, I have:

    Date currentDate = new Date();

    The following results in a syntax error because orderDate doesn’t have tick marks around the double quotes:

    ordersInsert = "INSERT INTO ORDERS " +
    "(ORDERID, CNUM, ORDERDATE, METHPMT, ORDERSOURCE, STATUS, SUBTOTAL) " +
    "VALUES (" + orderId + ", " + custNumber + ", " + orderDate + ", " +
    " '" + payType + "', '" + orderSource + "', '" + shipStatus + "', " +
    " " + subtotal + ") ";

    Error:
    INSERT INTO ORDERS (ORDERID, CNUM, ORDERDATE, METHPMT, ORDERSOURCE, STATUS, SUBTOTAL) VALUES (1106, 33, Thu Jul 03 15:18:45 EDT 2003, 'check', 'website', 'P', 67.5) <BR>

    SQLException: Could not execute the orders insert.
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Thu Jul 03 15:18:45 EDT 2003'.

    *****************************
    However, when tickmarks are added, it results in a data type mismatch.

    Insert: INSERT INTO ORDERS (ORDERID, CNUM, ORDERDATE, METHPMT, ORDERSOURCE, STATUS, SUBTOTAL) VALUES (1106, 33, 'Thu Jul 03 15:25:54 EDT 2003', 'check', 'website', 'P', 67.5) <BR>
    SQLException: Could not execute the orders insert.
    [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

    *******************************
    The following works for current dates but, of course, Date() is not something I formatted:

    ordersInsert = "INSERT INTO ORDERS " +
    "(ORDERID, CNUM, ORDERDATE, METHPMT, ORDERSOURCE, STATUS, SUBTOTAL) " +
    "VALUES (" + orderId + ", " + custNumber + ", Date(), " +
    " '" + payType + "', '" + orderSource + "', '" + shipStatus + "', " +
    " " + subtotal + ") ";
    ***************************************
    Any help you can offer would be greatly appreciated.

    Thanks,


    Robin
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2002
    Posts
    6
    Rep Power
    0
    try using java.text.SimpleDateFormat

    eg.
    Code:
    Calendar c1 = Calendar.getInstance(); // today
        String dateFormat = "MM/dd/yy";
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        String date = sdf.format(c1.getTime());
    Im not sure if the date you are using is today but you should be to format java.util.Date
  4. #3
  5. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
    Alternatively you could use a java.sql.Date:
    Code:
    java.util.Date now = new java.util.Date();
    
    java.sql.Date sqlNow = new java.sql.Date(now.getTime());
    
    String sqlStringDate = sqlNow.toString();
    This hasn't been tested but it should work without any major changes.
    ~ishnid
    Last edited by ishnid; July 9th, 2003 at 03:26 AM.
  6. #4
  7. No Profile Picture
    Clueless llama
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Feb 2001
    Location
    Lincoln, NE. USA
    Posts
    2,353
    Rep Power
    118
    Why not use a PreparedStatement? PreparedStatements format all input for the database driver you are using. No need to remember to escape special characters in strings or anything.

    Code:
    String ordersInsert =
    	"INSERT INTO ORDERS "
    		+ "(ORDERID, CNUM, ORDERDATE, METHPMT, ORDERSOURCE, STATUS, SUBTOTAL) "
    		+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
    		
    		PreparedStatement ps = con.prepareStatement(ordersInsert);
    		ps.setInt(1, orderId);
    		ps.setInt(2, custNumber);
    		ps.setDate(3, orderDate);
    		ps.setString(4, payType);
    		ps.setString(5, orderSource);
    		ps.setString(6, shipStatus);
    		ps.setDouble(7, subtotal);
    Note, I am guessing on some of the data types, but you get the idea.

    http://java.sun.com/j2se/1.3/docs/ap...Statement.html
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    New Jersey
    Posts
    100
    Rep Power
    13
    Thanks for all the responses.

    The code below also works for a date that is not a current date.

    Robin

    *******************************************************************************************
    String strDate = 11/03/03;

    DateFormat fmt = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
    GregorianCalendar calendar = new GregorianCalendar();

    String strCurrentFullYear = "" + calendar.get(calendar.YEAR);

    String strCentury = strCurrentFullYear.substring(0, 2) + "00";
    int intCentury = Integer.parseInt(strCentury);

    int intSlash1 = strDate.indexOf("/");
    int intMonth = Integer.parseInt(strDate.substring(0, intSlash1));

    String strTemp = strDate.substring(intSlash1 + 1, strDate.length());
    int intSlash2 = strTemp.indexOf("/");

    int intDay = Integer.parseInt(strTemp.substring(0, intSlash2));
    int intYear = Integer.parseInt(strTemp.substring(intSlash2 + 1, strTemp.length()));
    String strYear = "" + intYear;

    if (strCurrentFullYear.substring(2, 4).equals("99") && strYear.equals("00"))
    intCentury = intCentury + 1;

    int intFullYear = intCentury + intYear;

    GregorianCalendar cal = new GregorianCalendar(intFullYear, intMonth - 1, intDay);
    String strFormattedDate = fmt.format(cal.getTime());
  10. #6
  11. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
    That's an awfully long-winded way of going about it. You can make it much easier on yourself:
    Code:
    String strDate = "11/03/03";
    SimpleDateFormat sdf= new SimpleDateFormat("MM/dd/yy");
    java.sql.Date sqlDate = new java.sql.Date(sdf.parse(strDate).getTime());
    String sqlStringDate = sqlDate.toString();
    (again it's untested but should give you a good idea).

    ~ishnid
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Location
    New Jersey
    Posts
    100
    Rep Power
    13
    Ishnid,

    Thanks.

    Although the date 11/11/03 was actually inserted as '2003-11-11', it was formatted properly in the database itself.

    Also, just for the record, the following is a correction to my line of code:

    if (strCurrentFullYear.substring(2, 4).equals("99") && strYear.equals("00"))
    intCentury = intCentury + 100;

    Robin
  14. #8
  15. kill 9, $$;
    Devshed Supreme Being (6500+ posts)

    Join Date
    Sep 2001
    Location
    Shanghai, An tSín
    Posts
    6,898
    Rep Power
    3887
    Originally posted by RobinR

    Although the date 11/11/03 was actually inserted as '2003-11-11', it was formatted properly in the database itself.
    yyyy-mm-dd is actually the correct way of entering data into a database. It came about as an international standard to avoid confusion between the European dd/mm/yyyy system and the mm/dd/yyyy used in the US. As far I know, Access will the format the date whatever way you specify whenever it's outputting it.

    ~ishnid

IMN logo majestic logo threadwatch logo seochat tools logo