SQL INSERT system date into table

Question

I have created a table called "myTable" with a column called CURRENT_DATE and it's data type is "DATE". When I programatically update my table, I also want to place a timestamp or system date in the CURRENT_DATE field. A portion of what I am doing is shown below, but it is not working. I would think this would be easy, but... Can you help?

//System date is captured for placement in the CURRENT_DATE field in myTable
 java.util.Date currentDate = new java.util.Date();
...
stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', ' " + currentDate + " ')  ");

Dunderklumpen · Accepted Answer · 2010-07-12 06:56:23Z

9

You really should be doing this as a prepared statement using parameters, it makes things a lot easier and cuts out a few very simple SQL injection threats.

Connection con = ...;
PreparedStatement statement = con.prepareStatement("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES ( ?, ?)");
statement.setString(1, userName);
statement.setDate(2, currentDate );
statement.execute();

There is plenty of info on how to use prepared statements properly. For example: http://www.jdbc-tutorial.com/jdbc-prepared-statements.htm

answered Jul 12, 2010 at 6:56

Dunderklumpen

1,8743 gold badges17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David Hedlund · Accepted Answer · 2010-07-12 06:50:10Z

3

If you just want the current date, you can retrieve that from the SQL server, without submitting it as a variable. It varies a little depending on what server you're using, but in MS SQL Server there's a function called getdate().

stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', getdate()");

You can also set getdate() as the default value for that field, so that you can omit the field entirely:

stmt.executeQuery("INSERT INTO myTable (NAME) VALUES (' " + userName + " '" ')  ");

answered Jul 12, 2010 at 6:50

David Hedlund

130k33 gold badges205 silver badges223 bronze badges

Comments

onof · Accepted Answer · 2010-07-12 06:50:52Z

3

Don't put strings in the SQL. Use a prepared statement and set parameters.

answered Jul 12, 2010 at 6:50

onof

17.4k7 gold badges52 silver badges86 bronze badges

3 Comments

krock Over a year ago

+1 for the only answer not vulnerable to SQL injection attacks

David Hedlund Over a year ago

This should be a comment, as it's totally peripheral to the question being asked. But having said that, i agree, of course =)

onof Over a year ago

@David Hedlund: it's not peripheral: putting a date renderized to string directly in SQL could be misinterpreted by the DBMS, because you don't know the DBMS that code is actually querying.

Matt Mitchell · Accepted Answer · 2010-07-12 06:50:15Z

0

Try this:

stmt.executeQuery("INSERT INTO myTable (NAME, CURRENT_DATE) VALUES (' " + userName + " ', GETDATE())  ");

answered Jul 12, 2010 at 6:50

Matt Mitchell

42k35 gold badges122 silver badges186 bronze badges

Comments

paxdiablo · Accepted Answer · 2010-07-12 06:50:48Z

0

For data integrity, this is something that should be done at the DBMS end with an update and insert trigger.

Any other method can be gotten around by malicious (or buggy) code.

How to get the actual value (and how to create the appropriate triggers) depends on the DBMS itself. DB2 provides current date, current time and current timestamp that you can use to insert the server time into the database, which is likely to be more consistent than hundreds of different client times.

answered Jul 12, 2010 at 6:50

paxdiablo

889k243 gold badges1.6k silver badges2k bronze badges

Collectives™ on Stack Overflow

SQL INSERT system date into table

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related