CODEWITHSUNDEEP
javasqlitedatejdbc
yaylitzis
yaylitzis

Reputation: 5534

Create a variable Date in format "yyyy-mm-dd"

I am trying to insert to my table, a date in format "yyyy-mm-dd". My table has a field date type DATETIME and I use SQLite DB SYSTEM. I need to be in this format, so later to have the option to select dates from - then.

My table:

CREATE TABLE "IsSELECTED"(
           "date" DATETIME PRIMARY KEY  NOT NULL  DEFAULT (CURRENT_DATE) ,
           "morning" BOOL NOT NULL  DEFAULT (0) ,
           "noon" BOOL NOT NULL  DEFAULT (0) ,
           "night" BOOL NOT NULL  DEFAULT (0) 
     )

and the method which i am trying to insert.

        public void setTodayInfo(HttpSession session, Date date) 
            throws SQLException, ClassNotFoundException {

        System.out.println("Initialize today's info...");
        String sq = "INSERT INTO IsSELECTED (date, morning, noon, night) VALUES (?, ?, ?, ?)";

        try {       
            Class.forName(typeDB);
            c = DriverManager.getConnection(path);            
            stm = c.prepareStatement(sq);

            PreparedStatement stm = c.prepareStatement(sq);

            stm.setDate(1, date);
            stm.setBoolean(2, FALSE);
            stm.setBoolean(3, FALSE);
            stm.setBoolean(4, FALSE);

            int rowsAffected = stm.executeUpdate();

        } catch (SQLException e) { 
        System.out.println(e.getMessage());
        } finally {
        if (stm != null) {
            stm.close();
        }
        if (c != null) {
            c.close();
        }
    }          
    }

How can i create a variable (DATE) with the format "yyyy-mm-dd" ?

Upvotes: 1

Views: 2840

Answers (2)

JamesB
JamesB

Reputation: 7894

You don't need to worry about the format of the date when storing it. The database will simply store the date instance and you can use one of its functions to format it on retrieval.

SQL Lite date functions: https://www.sqlite.org/lang_datefunc.html

If you don't want to be tied to the functions of a particular database vendor, you could also use Java to format the date on retrieval using SimpleDateFormat: http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html

Upvotes: 1

Davide Lorenzo MARINO
Davide Lorenzo MARINO

Reputation: 26926

You have to create a DateFormat instance and work with it. Use format to get the String from a Date. Use parse to get the Date from the String. Note that probably you need the format yyyy-MM-dd and not yyyy-mm-dd. MM means months, mm means minuts

Date now = new Date();
DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
System.out.println(df.format(now)); // Prints today date in yyyy-MM-dd format
Date firstJune = df.parse("2015-06-01");

In the database the field is a Date field so you have to save the data converting String to Date or directly savings a Date.

Note the format is applied to the String representation of your Date

Upvotes: 0

Related Questions