Format SQL Server Dates

[jamojo]

Hello everyone,

How can I convert this format using Java?

2009-05-20T18:19:23+08:00

I was able to convert this using the SQL Statement below.

select cast('' as xml).value('xs:dateTime('2009-05-20T18:19:23+08:00')', 'datetime');

I would like to know how Java handle this format.

Any suggestions are greatly appreciated.

Thanks in advance,

Jamojo

[masijade]

Read the API docs for SimpleDateFormat.

[jamojo]

Thanks for the reply.

This is what I have done so far. I thought that there would be a simplier approach or some function to use. I hope there's simple solution.

String StringToReturn = "";
String ColValue = "2009-05-20T18:19:23+08:00";	
int indexOfT = ColValue.indexOf("T");
System.out.println(indexOfT);
String dbDate = ColValue.substring(0,indexOfT);
System.out.println(dbDate);
String dbTime = ColValue.substring(indexOfT + 1 , ColValue.indexOf("+"));
System.out.println(dbTime);
String dbDateTime = dbDate + " " + dbTime;
StringToReturn = "cast('" + dbDateTime + "' as datetime)";
//System.out.println(StringToReturn);
DateFormat dfm = new SimpleDateFormat("yyyy-MM-ddH:mm:ss");
Date a = dfm.parse(dbDateTime);
System.out.println(a);

Thanks

[masijade]

Well, looking at the API docs the last example is

"yyyy-MM-dd'T'HH:mm:ss.SSSZ"  	"2001-07-04T12:08:56.235-0700"

This seems to be extremely close to what you need.

String value = "2009-05-20T18:19:23+08:00";
value = value.replaceFirst(":(\\d\\d)\\s*$", "$1");
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssZ");
Date date = sdf.parse(value);

Also, is this "String" being read from a "Date" column out of the DB? If so, why are you using getString to read it rather than getDate?

[javaAddict]

Actually if you use the getDate(), it will return a java.sql.Date, which doesn't hold the milliseconds.
When I want to get date from database I usually do this at the query:

TO_CHAR('format', date_column);

And then use the ResultSet.getString() method

[jamojo]

Also, is this "String" being read from a "Date" column out of the DB? If so, why are you using getString to read it rather than getDate?

The string is basically from a flat file generated by another SQL Server station. Then this string should be in inserted to another SQL Server station.

INSERT INTO CUSTOMERS(CID,CNAME,ENTERDATE) VALUES (101,'MYNAME',cast('2009-05-20 18:14:32' as datetime))

But I cannot convert this format as a date:
CAST('2009-05-20T18:19:23+08:00' AS DATETIME)

So I wished to convert this to date in Java.

Thanks for all of your replies.

[masijade]

Actually if you use the getDate(), it will return a java.sql.Date, which doesn't hold the milliseconds.
When I want to get date from database I usually do this at the query:

TO_CHAR('format', date_column);

And then use the ResultSet.getString() method

Then use getTimestamp if some part of the Date is really getting cut using getDate.

Edit: IOW, without an extremely good reason, retreive the proper type for the column involved. There should, in 99.999999% of all cases, be no reason to have to play with these conversions when retreiving info from a DB.

[masijade]

Also, although the API docs for java.sql.Date state that the time will be rounded, in my experience, it has always only done that when formatting a String representation of the Date, and that the actual Date object still retains the entire Date value. Try the following and see if that is not the case

public class UtilSqlDateTester {
	public static void main(String[] args) {
		java.util.Date d = new java.util.Date();
		System.out.println(d);
		java.sql.Date s = new java.sql.Date(d.getTime());
		System.out.println(s);
		java.util.Date sd = new java.util.Date(s.getTime());
		System.out.println(sd);
	}
}

[jamojo]

IOW, without an extremely good reason, retreive the proper type for the column involved. There should, in 99.999999% of all cases, be no reason to have to play with these conversions when retreiving info from a DB.

Yeah, I got some idea with that, I created a class to use this SQL Statement to check the column involved before converting:

select data_type, character_maximum_length from information_schema.columns where UPPER(table_name) = 'customer' and UPPER(column_name) = 'enterdate'

Thanks for your reply.

[masijade]

Uhm, sorry, but both DatabaseMetaData (before executing a select), and ResultSetMetaData (after executing a select) can both give you this information in a much, much, much more db independent way.

[jamojo]

Uhm, sorry, but both DatabaseMetaData (before executing a select), and ResultSetMetaData (after executing a select) can both give you this information in a much, much, much more db independent way.

Yeah, I read what you mentioned. ResultSetMetaData and DatabaseMetaData in the API Doc. I agree, I don't need the select clause that I mentioned to be more db independent way to validate columns. I'll just play around with these functionalities.

Could you give me some good reference regarding these topics or JDBC? If its okay with you.

Thanks very much.

[masijade]

I can't speak to its quality, but there's this one

http://www.java2s.com/Tutorial/Java/0340__Database/DatabaseMetadata.htm

All I can say is to google "databasemetadata resultmetadata tutorial", but steer clear of anything from roseindia.