Java convert date to sql timestamp

Question

I'm trying to convert a date (string) extracted from a csv file, convert it to sql timestamp and upload using prepared statement. What I have is:

String test = "8/10/2014 16:59";

DateFormat fromFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm");
fromFormat.setLenient(false);
DateFormat toFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss.SSSSSS");
toFormat.setLenient(false);
Date date2 = null;
try {
    date2 = toFormat.parse(test);
} catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

//java.sql.Timestamp ts2 = java.sql.Timestamp.valueOf(date2);
//java.sql.Timestamp sqlDate2 = new java.sql.Timestamp(timestamp); 
//sql_statement.setTimestamp(1, ts2);

As you can see my code is messy as I'm trying to solve this problem. I'm always getting an error in eclipse:

java.text.ParseException: Unparseable date: "8/10/2014 16:59"
at java.text.DateFormat.parse(DateFormat.java:357)
at      com.syntronic.client.thread.ORCThreadTejasInv.uploadOracleDBOptical(ORCThreadTejasInv.java:555)
at com.syntronic.client.thread.ORCThreadTejasInv.connectOracleDB(ORCThreadTejasInv.java:170)
at com.syntronic.client.thread.ORCThreadTejasInv.retrieveOracleTejas(ORCThreadTejasInv.java:125)
at com.syntronic.client.thread.ORCThreadTejasInv.run(ORCThreadTejasInv.java:84)
at java.lang.Thread.run(Thread.java:745)

I even try using:

SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'" );
String yourformattedDate = sdf.format(test);

and diff error shows up"

Exception in thread "Thread-6" java.lang.IllegalArgumentException: Cannot format given Object as a Date
at java.text.DateFormat.format(DateFormat.java:301)
at java.text.Format.format(Format.java:157)
at com.syntronic.client.thread.ORCThreadTejasInv.uploadOracleDBOptical(ORCThreadTejasInv.java:562)
at com.syntronic.client.thread.ORCThreadTejasInv.connectOracleDB(ORCThreadTejasInv.java:170)
at com.syntronic.client.thread.ORCThreadTejasInv.retrieveOracleTejas(ORCThreadTejasInv.java:125)
at com.syntronic.client.thread.ORCThreadTejasInv.run(ORCThreadTejasInv.java:84)
at java.lang.Thread.run(Thread.java:745)

Anyone can help on why the date is unparseable? and how to convert it to a proper sql timestamp? thank you

Why do you think your first attempt should work? How are you parsing your date? — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Oct 9, 2014 at 4:56
You can only format Date or Number objects with DateFormat#format(), not String objects. — Sotirios Delimanolis
– Sotirios Delimanolis, Commented Oct 9, 2014 at 4:56

Elliott Frisch · Accepted Answer · 2014-10-09 05:05:03Z

3

Your fromFormat format specifier is

dd/MM/yyyy hh:mm

but should be

dd/MM/yyyy HH:mm

And change the toFormat to yyyy-MM-dd HH:mm:ss.SSSSSS

Then your parse code should change from

date2 = toFormat.parse(test);

to

date2 = fromFormat.parse(test);
System.out.println(toFormat.format(date2));

And I get the output

2014-10-08 04:59:00.000000

answered Oct 9, 2014 at 5:05

Elliott Frisch

202k20 gold badges166 silver badges265 bronze badges

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

Comments

Nishant Modi · Accepted Answer · 2014-10-09 05:18:32Z

2

Please use following code it will serve your need

        String test = "8/10/2014 16:59";
        Date date2 = null;
        SimpleDateFormat fromFormat = new SimpleDateFormat("dd/MM/yyyy hh:mm");


        try
        {

            date2 = fromFormat.parse(test);
            Timestamp tt = new Timestamp(date2.getTime());
            System.out.println(tt);

        } catch (ParseException ex)
        {

            date2 = null;
        }

edited Oct 9, 2014 at 5:18

answered Oct 9, 2014 at 5:07

Nishant Modi

6791 gold badge6 silver badges20 bronze badges

1 Comment

Nir Alfasi Over a year ago

"it will server your need" - only for this sentence you deserve +1 :)

Nir Alfasi · Accepted Answer · 2014-10-09 05:23:56Z

2

When you parse a date-string that looks like this:

String test = "8/10/2014 16:59";

you're using 24-hours format (16:59) you should use HH instead of hh.

See the following code snippet:

    DateFormat fromFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm");
    fromFormat.setLenient(false);
    Date date2 = null;
    try {
        date2 = fromFormat.parse(test);
        System.out.println("date2 = " + date2);  // prints date2 = Wed Oct 08 16:59:00 PDT 2014          
    } catch (ParseException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

edited Oct 9, 2014 at 5:23

answered Oct 9, 2014 at 5:03

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

9 Comments

MadProgrammer Over a year ago

You could use d/M/y H:M and get the same result, the number markers are irrlevent for parsing, they're only really used for formatting...

Nir Alfasi Over a year ago

@MadProgrammer not true: try running the code snippet I provided in the answer with the format d/M/y H:M and you'll get a ParseException

MadProgrammer Over a year ago

Input 8/10/2014 16:59, format d/M/y H:m, output date2 = Wed Oct 08 16:59:00 EST 2014 ...

Nir Alfasi Over a year ago

@MadProgrammer I have to admit that I didn't test it on Java 7, but on Java 8 it fails with: "java.text.ParseException: Unparseable date: "08/10/2014 16:59" at java.text.DateFormat.parse"

Nir Alfasi Over a year ago

@MadProgrammer The first example you posted fails because you've used M instead of m (minutes). Using d/M/y H:m works for me too. Anyways, thanks - point taken and I'll fix the answer respectfully.

|

Barranka · Accepted Answer · 2014-10-09 05:45:17Z

So you have a String, and you want to parse it as a Date... Let's see if this example helps you:

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

public class DateConverter
{
    public static void main(String[] args) {
        String test = "8/10/2014 16:59";
        SimpleDateFormat sdf1 = new SimpleDateFormat("d/MM/yyyy HH:mm"),
                         sdf2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        /*
         'sdf1' will be used to parse your input string as a date
         'sdf2' will be used to output a string with your desired date format
         */
        Date d;
        String formatted_date;
        try {
            // Parse the string to a date, using the defined format
            d = sdf1.parse(test);
            // Now, format the date with 'sdf2' and store it in a string
            formatted_date = sdf2.format(d);
            System.out.println(formatted_date); // The output is: 2014-10-08 16:59:00
        } catch(ParseException e) {
            System.err.println(e.getMessage());
            e.printStackTrace(System.err);
        }
    }
}

Arnold Cristobal · Accepted Answer · 2014-10-09 05:30:49Z

1

Was able to fix it using the code below:

DateFormat fromFormat = new SimpleDateFormat("dd/MM/yyyy HH:mm");
Date date2 = null;
String def = perRow[cnt].replaceAll("8", "08");
try {
    date2 = fromFormat.parse(def);
} catch (ParseException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
}

long tsTime1 = date2.getTime();

java.sql.Timestamp sqlDate2 = new java.sql.Timestamp(tsTime1); 
sql_statement.setTimestamp(2, sqlDate2);

Of course, I don't know if this is the correct or proer way to it as:
1. replace the sting with correct day 'dd'
2. parse to date format
3. convert to long
4. convert to sql date

Anyone knows a better way or idea, thread is open for comments. thank you.

answered Oct 9, 2014 at 5:30

Arnold Cristobal

8512 gold badges17 silver badges40 bronze badges

1 Comment

MadProgrammer Over a year ago

Why?? You could use a format of d/M/y H:m and it would parse 8/10/2014 16:59 successfully. The major problem is not using H...simple fix, not need to stuff around with replaceAll...

Nisheeth Shah · Accepted Answer · 2014-10-09 06:06:09Z

0

You can convert it using java.sql.Timestamp. Here is a snippet:

    String strDate = "15/07/1989 15:30";
    SimpleDateFormat format = new SimpleDateFormat("dd/MM/yyyy hh:mm");
    Date date = format.parse(strDate);
    System.out.println(date);
    java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
    System.out.println(timestamp);

And the output will be:

Sat Jul 15 15:30:00 IST 1989
1989-07-15 15:30:00.0

answered Oct 9, 2014 at 6:06

Nisheeth Shah

6081 gold badge10 silver badges22 bronze badges

Comments

faheem farhan · Accepted Answer · 2014-10-09 06:38:26Z

0

Simple!

First parse fromDate then format in toDate pattern.

toFormat.format(fromFormat.parse(test));

answered Oct 9, 2014 at 6:38

faheem farhan

4112 silver badges10 bronze badges

Collectives™ on Stack Overflow

Java convert date to sql timestamp

7 Answers 7

Comments

1 Comment

9 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

1 Comment

9 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related