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

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.

Letter Date or Time Component Examples
G Era designator AD
y Year 1996; 96
Y Week year 2009; 09
M Month in year July; Jul; 07
w Week in year 27
W Week in month 2
D Day in year 189
d Day in month 10
F Day of week in month 2
E Day name in week Tuesday; Tue
u Day number of week (1 = Monday, …, 7 = Sunday) 1
a Am/pm marker PM
H Hour in day (0-23) 0
k Hour in day (1-24) 24
K Hour in am/pm (0-11) 0
h Hour in am/pm (1-12) 12
m Minute in hour 30
s Second in minute 55
S Millisecond 978
z Time zone Pacific Standard Time; PST; GMT-08:00
Z Time zone -0800
X Time 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.

Leave a Comment