Date format in Database is in yyyy/MM/dd i want it in the from MM/dd/yyyy

Question

Hello in my web application i ve used SimpleDateFormat to convert string to date and in the format MM/dd/yyyy but when the form fields get inserted in the database its in the format yyyy/MM/dd.

Below is my servlet

protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    Affiliate af= new Affiliate();

    af.setFisrtName(request.getParameter("txtFname"));
    af.setLastName(request.getParameter("txtLname"));
    af.setGender(request.getParameter("txtGender"));
    af.setCategory(request.getParameter("txtCategory"));
    String dob=(request.getParameter("txtDob"));
    SimpleDateFormat formatter = new SimpleDateFormat("MM/dd/yyyy");  
    Date date;
    try {
        date = (Date)formatter.parse(dob);
        af.setDate(date);
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    af.setAge(Integer.parseInt(request.getParameter("txtAge")));
    af.setAddress(request.getParameter("txtAddr"));
    af.setCountry("India");
    af.setState(request.getParameter("txtState"));
    af.setCity(request.getParameter("txtCity"));
    af.setPinCode(Integer.parseInt(request.getParameter("txtPin")));
    af.setEmailId(request.getParameter("txtEmail"));
    af.setStd(Integer.parseInt(request.getParameter("txtStd")));
    af.setContactNo(Integer.parseInt(request.getParameter("txtPhone")));
    af.setMobileNo(Long.parseLong(request.getParameter("txtMobile"),10));

AffiliateService afs=new AffiliateService();
**afs.createAffiliate(af);**
}

Below is my DAO:

public void insertAffiliate(Affiliate affiliate){
    String sql="INSERT INTO REGISTER " +"(id,FisrtName,LastName,Gender,Category,DateOfBirth,Age,Address,Country,State,City,PinCode,EmailId,Std,ContactNo,MobileNo)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Connection conn = null;

    try {
        **conn = dataSource.createConnection();**
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setInt(1, affiliate.getId());
        ps.setString(2, affiliate.getFisrtName());
        ps.setString(3, affiliate.getLastName());
        ps.setString(4,affiliate.getGender());
        ps.setString(5, affiliate.getCategory());
        ps.setDate(6, new java.sql.Date(affiliate.getDate().getTime()));
        ps.setInt(7, affiliate.getAge());
        ps.setString(8, affiliate.getAddress());
        ps.setString(9,affiliate.getCountry());
        ps.setString(10,affiliate.getState());
        ps.setString(11, affiliate.getCity());
        ps.setInt(12, affiliate.getPinCode());
        ps.setString(13, affiliate.getEmailId());
        ps.setInt(14,affiliate.getStd());
        ps.setInt(15, affiliate.getContactNo());
        ps.setLong(16, affiliate.getMobileNo());

        ps.executeUpdate();
        ps.close();

    } catch (SQLException e) {
        throw new RuntimeException(e);

    } finally {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {}
        }
    }
}

And below is my DTO:

public class Affiliate {

@NotNull
    @Past
    Date date;

public Date getDate() {
        return date;
    }
    public void setDate(Date date) {
        this.date = date;
    }

not sure what you want but maybe this can help http://stackoverflow.com/questions/4052862/how-to-change-mysql-date-format-for-database — Leo, Feb 05 '14 at 11:28
i mean though i have set SimpleDateFormat to MM/dd/yyyy in database date is sitting in the format yyyy/MM/dd.. — user3222718, Feb 05 '14 at 11:31
exactly, so I wonder you want to change the way the date is displayed in the database, not in your java app, right? — Leo, Feb 05 '14 at 11:33
yes i need the database to store date in the format MM/dd/yyy — user3222718, Feb 05 '14 at 11:36
but you know, the database does not actually store date like that. it probably stores as millis and just show like that. In that case, you can't change mysql default date format. You can still use DATE_FORMAT in your select. — Leo, Feb 05 '14 at 11:37
@user3222718 Usually we pull the date-time value from the database into Java as a date-time object, *not as text*. Your JDBC driver does that work for you. Then on the Java side, we use that date-time object to create string representations in whatever format we want. Search StackOverflow for many examples of formatting a string from a date in Java. Tip: Search for the word "joda" as well. — Basil Bourque, Feb 05 '14 at 12:13
Ah the day where you realize that data is not the same as PRESENTING data. Such a wonderful day where things just become so much more clear design-wise. — Gimby, Feb 05 '14 at 13:10

score 3 · Accepted Answer · answered Feb 05 '14 at 11:33

3

If you're storing it as a Date then its DB dependent. If you're storing it as a String, then you can format it using SimpleDateFormat in the format you want and then store it

answered Feb 05 '14 at 11:33

Abylay Sabirgaliyev

726
9
18

Nope - a Date is a Date is a Date: some point in time stored in the database. How it is displayed is just the clients choice. Storing a date as String is always showing a non-understanding of database concepts. Never do that. – Gyro Gearless Feb 05 '14 at 11:38

score 0 · Answer 2 · answered Feb 05 '14 at 11:33

Year-month-day is the standard format for dates in databases. The date column does not store the format of course, it stores the date and any formatting is applied by client software. Some database clients (Oracle) permit defining a custom date format, but not MySQL.

You can always format the date in your client application when it gets the results from the database.

SimpleDateFormat myFormat = new SimpleDateFormat("MM/dd/yyyy");
Date date = resultSet.getDate(column);
String dateString = myFormat.format(date);

You can also apply formatting function in the database, with the DATE_FORMAT function.

score 0 · Answer 3 · answered Feb 05 '14 at 13:08

0

You can't change mysql default date format.

You can still use DATE_FORMAT in your select.

answered Feb 05 '14 at 13:08

Leo

6,480
4
37
52

Date format in Database is in yyyy/MM/dd i want it in the from MM/dd/yyyy

3 Answers3