How to convert java.util.Date to Mysql Date Format

10 December 2011 By admin 13,692 views 2 Comments
0 Flares Twitter 0 Facebook 0 Google+ 0 0 Flares ×

MySQL’s default DATE field format is

YYYY-MM-DD

and DATETIME type is a date and time combination which stores data in

YYYY-MM-DD HH:MM:SS

format.

Whereas in Java, the Date class’ (available in java.util package) default format is,

dow mon dd hh:mm:ss zzz yyyy

where:

  • dow is the day of the week (Sun, Mon, Tue, Wed, Thu, Fri, Sat).
  • mon is the month (Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec).
  • dd is the day of the month (01 through 31), as two decimal digits.
  • hh is the hour of the day (00 through 23), as two decimal digits.
  • mm is the minute within the hour (00 through 59), as two decimal digits.
  • ss is the second within the minute (00 through 61, as two decimal digits.
  • zzz is the time zone (and may reflect daylight saving time). Standard time zone abbreviations include those recognized by the method parse. If time zone information is not available, then zzz is empty – that is, it consists of no characters at all.
  • yyyy is the year, as four decimal digits.

So, there is a need to convert the java’s default Date format to Mysql’s date format. The conversion can be done at the database level (through various mysql functions) or at the program level through some java code. We will discuss only the latter part.

Java provides a class called SimpleDateFormat available in java.text package which allows for formatting (date -> text) through format() method and parsing (text -> date) through parse() method.

Here, we will convert the today’s date from java Date object to mysql date format.

1. Create a Date object.

Date now = new Date();

2. Create a SimpleDateFormat object by using the constructor,

public SimpleDateFormat(String pattern)

Constructs a SimpleDateFormat using the given pattern and the default date format symbols for the default locale. Note: This constructor may not support all locales. For full coverage, use the factory methods in the DateFormat class.

Parameters:
pattern – the pattern describing the date and time format

String pattern = "yyyy-MM-dd";
SimpleDateFormat formatter = new SimpleDateFormat(pattern);

3. Now use the format() method to convert the date object to text format provided in the pattern.

String mysqlDateString = formatter.format(now);

Complete Code:

import java.text.SimpleDateFormat;
import java.util.Date;

public class JavaToMysqlDate {
	public static void main(String[] args) {
		Date now = new Date();
		String pattern = "yyyy-MM-dd";
		SimpleDateFormat formatter = new SimpleDateFormat(pattern);
		String mysqlDateString = formatter.format(now);
		System.out.println("Java's Default Date Format: " + now);
		System.out.println("Mysql's Default Date Format: " + mysqlDateString);
	}
}

The following pattern letters are defined in SimpleDateFormat.

LetterDate or Time ComponentExamples
GEra designatorAD
yYear1996; 96
YWeek year2009; 09
MMonth in yearJuly; Jul; 07
wWeek in year27
WWeek in month2
DDay in year189
dDay in month10
FDay of week in month2
EDay name in weekTuesday; Tue
uDay number of week (1 = Monday, …, 7 = Sunday)1
aAm/pm markerPM
HHour in day (0-23)0
kHour in day (1-24)24
KHour in am/pm (0-11)0
hHour in am/pm (1-12)12
mMinute in hour30
sSecond in minute55
SMillisecond978
zTime zonePacific Standard Time; PST; GMT-08:00
ZTime zone-0800
XTime zone-08; -0800; -08:00

So for Mysql DateTime format, you can use the pattern “yyyy-MM-dd HH:mm:ss”

For converting date in String format to Date object, refer this tutorial.

  • Sumeet Shahapeti

    Thanks

  • roja

    this is very useful and easily understandable