How do I convert a Joda Time DateTime object into a String in SQL Server format?

Question

I am using the Joda-Time library in Java, and have a date and time stored as an org.joda.time.DateTime object.

How can I reliably convert this DateTime object into a String that will be parsed correctly by SQL server (including timezone), such that I can use it in an INSERT SQL statement?

Answer 1

you can try this simple code :

DateTime dt = new DateTime();
DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
String dtStr = fmt.print(dt);

Answer 2

Use java.sql.Timestamp with PreparedStatement#setTimestamp().

ps.setTimestamp(1, new Timestamp(dateTime.getMillis()));

Note that java.sql.Date stores only the date part, not the time part.

Answer 3

Be careful to consider time zones. Using new Timestamp() may be tricky since it expects time in milliseconds in GMT.

    DateTime dt = new DateTime(2010, 1, 1, 14, 30, 59, 1, DateTimeZone.forOffsetHoursMinutes(7, 0));
    Timestamp ts = new Timestamp(dt.getMillis());
    System.out.println(dt); // prints 2010-01-01T14:30:59.001+07:00
    System.out.println(ts); // prints 2010-01-01 08:30:59.001

    DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
    String sqlTimeString = fmt.print(dt);
    System.out.println(sqlTimeString); // prints 2010-01-01 14:30:59

Answer 4

Simply Function

I use this simple function to get a SQL-friendly date format from a JodaTime DateTime object:

// Converts a DateTime object into a SQL-format friendly string.
//
// Return format looks like this: 2014-01-22 10:05:34.546
//
public static String toSql(DateTime dateTime) {
    return new Timestamp( dateTime.getMillis() ).toString();
}