CODEWITHSUNDEEP
javasqldate
Jordan.J.D
Jordan.J.D

Reputation: 8113

Java date formatting for SQL

I have a date object I constructed with:

    Calendar cal = new GregorianCalendar();
    cal.set(2013, 12, 1, 0, 0, 0);
    Date begin = new Date(cal.getTimeInMillis());

I am trying to use it in a query and I know the query takes the format like: 2008-10-29 14:56:59

When I print my date it looks like: Wed Jan 01 00:00:00 EST 2014

My question:

How do I convert the date format from Wed Jan 01 00:00:00 EST 2014 to 2008-10-29 14:56:59

Upvotes: 0

Views: 148

Answers (4)

Basil Bourque
Basil Bourque

Reputation: 338574

Use objects rather than strings when communicating with your database. For a date-time value, use a date-time object.

java.time

Avoid the troublesome old date-time classes including Calendar and GregorianCalendar. They are poorly-designed, flawed, and confusing. Among their troubles is toString methods that dynamically apply the JVM’s current default time zone when generating a string, meant to be helpful but turns out to be an anti-feature.

Instead, use the modern java.time classes.

If your JDBC driver is updated for JDBC 4.2 and later, use the java.time types directly rather than the java.sql types.

Alter your input string to comply with ISO 8601 format. Change that SPACE in middle to T. 

String input = "2008-10-29 14:56:59".replace( " " , "T" ) ;

Parse as a LocalDateTime since your input lacks any indication of time zone or offset-from-UTC.

LocalDateTime ldt = LocalDateTime.parse( input ) ;

Or specify the date-time value as integers.

LocalDateTime ldt = LocalDateTime.of( 2013 , 12 , 1 , 0 , 0 , 0 , 0 ) ;

If your database column is of type TIMESTAMP WITHOUT TIME ZONE, you are ready to query. Be sure you understand that without the context of a time zone or offset-from-UTC, such values do not represent a specific moment, and are not a point on the timeline, rather they are vague ideas about possible moments.

String sql = "SELECT * FROM tbl_ WHERE when_ >= ? ; " ; 
PreparedStatement ps = conn.prepareStatement( sql ) ;
ps.setObject( 1 , ldt ) ;  // Call `setObject` to pass a java.time object directly without converting into `java.sql.*` type.
ResultSet resultSet = ps.executeQuery() ;
…
LocalDateTime ldt = resultSet.getObject( … , LocalDateTime.class ) ;

If you had intended to track actual moments, specific points on the timeline, then you would be using TIMESTAMP WITH TIME ZONE as your database column type, and using Instant and ZonedDateTime java.time classes.

Upvotes: 0

pravi
pravi

Reputation: 444

Use this to get the format you want

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String dateInString = "pass you date string here";
Date date = sdf.parse(dateInString);

Hope this helps.

Upvotes: 0

Kaustubh Khare
Kaustubh Khare

Reputation: 3510

You can use simpleDateFormat class. I given simple example below.

Here you have to provide your required date format, Date object.

SimpleDateFormat dateformat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
String date =null;    
date = dateformat.format(begin);
System.out.println(date);

Upvotes: 0

Denis Kulagin
Denis Kulagin

Reputation: 8906

This should help:

Calendar cal = new GregorianCalendar();
cal.set(2013, 11, 1, 0, 0, 0);
DateFormat dateFormat;
dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
System.out.println(dateFormat.format(new Date(cal.getTimeInMillis())));
  • 11 is DECEMBER

Upvotes: 3

Related Questions