Java转换日期到sql时间戳。

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:

我正在尝试将从csv文件中提取的日期(字符串)转换为sql时间戳，并使用事先准备好的语句上载。我是:

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:

当我试图解决这个问题时，您可以看到我的代码是混乱的。我总是在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"

diff错误出现"

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

谁能帮忙解释一下为什么日期是不可逾越的?以及如何将其转换为适当的sql时间戳?谢谢你！

7 个解决方案

#1

Your fromFormat format specifier is

您的fromFormat格式说明符是。

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

然后将toFormat更改为yyyy-MM-dd HH:mm: ssssssss。

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

#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;
        } 

#3

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.

你使用的是24小时格式(16:59)，你应该用HH代替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();
    }

#4

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);
        }
    }
}

#5

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

当然，我不知道这是正确的还是更有效的方法:1。用正确的“dd”2代替刺。解析到日期格式3。转换为长4。转换为sql日期

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

任何人都知道一个更好的方法或想法，线程是开放的评论。谢谢你！

#6

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

您可以使用java.sql.Timestamp来转换它。这是一个片段:

    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

#7

Simple!

简单!

First parse fromDate then format in toDate pattern.

首先解析fromDate然后格式化为toDate模式。

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