• Post Reply Bookmark Topic Watch Topic
  • New Topic

Convert Oracle String to Java Date  RSS feed

 
Jeremy McNally
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a date which is being stored as a String in an Oracle Database. I need to extract this value in my Java program (okay that is fine), but once it is in my Java program, how do I convert it to a DATE to be used in a SQL Server script for date comparisons?

I have an example below:

Oracle String: 2011/04/24 12:26:53

Format it needs to be to be used by SQL Server (Same format): 2011/04/24 12:26:53


ISSUE:

Retrieve String-Date from Oracle and convert it to a date type with the same format. (So SQL Server won't throw an error).


I am in need of a real example with a great explanation. I have looked online and have seen nothing that I can put together mentally in order to accomplish this while still keeping the HH:MI:SS: (I do also have milliseconds) Not shown here but it would help...



 
Campbell Ritchie
Marshal
Posts: 56529
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeremy McNally wrote:I have a date which is being stored as a String in an Oracle Database. . . .
Why? Oracle will happily store dates as dates.

Don't use java.util.Date any more. Use the classes of the new Date and Time packages.
In that link you find how to parse that String to a date, or you can keep the time like this:-
LocalDateTime dateTime = LocalDateTime.parse(text, DateTimeFormatter.ISO_LOCAL_DATE_TIME);

That isn't exactly what you want, but very close. You can either change the format of the String or create a new parser with the symbols shown in DateTimeFormatter, which seem similar to the letters in the older SimpleDateFormat class.
 
Campbell Ritchie
Marshal
Posts: 56529
172
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you are retrieving that as a String and passing the String to SQL Server, what is the problem? The two look the same to me. Apart from the fact you shouldn't use Strings for such data in the first place.
 
Jeremy McNally
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If I store it in Oracle as a date, I'm not able to get the time back. I am only able to get the date out of it. If I store it in Oracle as a String and convert it to a date at that point I can then pass it to the SQL Server script to do my comparisons I need. Initially, I tried to pass it into the script as a String since all I thought it cared about was just the format. I believe there is an underlying JDBC thing that stops this from happening. Otherwise it wouldn't be an issue.
What makes me come to this conclusion is that, when I do a SQL Server sub query, and grab a the date in the same format from a column in the SQL Server DB I'm working with, it works find. EX: Replace that date you see in this post with SET @StartTime = SELECT MAX(StartTime) as StartTime FROM [UICTransfer_Archive].[ETC.].[StageTable]; (Still the same format but trying to do this with that date retrieved or created outside SQL Server, its proving to be a pain)... I'll check out what you mentioned at work tomorrow and see what I come up with. I'll respond with results or questions after I give it a shot.
 
Jeremy McNally
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Also, thank you for your response.
 
Paul Clapham
Sheriff
Posts: 22823
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeremy McNally wrote:If I store it in Oracle as a date, I'm not able to get the time back. I am only able to get the date out of it.


If you use a DATE type in your Oracle table then you should use the getTimestamp() method of ResultSet to get the date and time. If you use the getDate() method then you'll only get the date part -- perhaps that's what you did?
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oracle also has a proper TIMESTAMP datatype.
I know there's not a huge difference between that and the DATE one, but I seem to remember it being recommended that if time is important then you should use a TIMESTAMP rather than a plain old DATE.

Either way, you do need to use getTimestamp().
 
Jeremy McNally
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
In that case, I would want to change the datatype in Oracle to be TIMESTAMP and then use the getTimeStamp() method of the ResultSet class to get the date/time while storing it in a variable of type Timestamp. If I'm off on anything I just said, please let me know.
I'll be working on the solution today based off the feedback I received on this post and I'll keep this thread updated with the results and or questions pertaining to it.

Thank you for your response.
 
Jeremy McNally
Ranch Hand
Posts: 108
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One question I have... when I retrieve the TIMESTAMP and store it in a Timestamp variable after retrieving the date/time from the ResultSet, do you know if I'll need to do anything else with it before passing it into the script in SQL Server? If not, I'll find out anyway by trial and error...
 
Paul Clapham
Sheriff
Posts: 22823
43
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jeremy McNally wrote:In that case, I would want to change the datatype in Oracle to be TIMESTAMP and then use the getTimeStamp() method of the ResultSet class to get the date/time while storing it in a variable of type Timestamp. If I'm off on anything I just said, please let me know.


You could be right. I'm not an Oracle DB user but when I looked at their documentation about DATE columns it said that DATE contained date and time to the nearest second, and TIMESTAMP contained that plus milliseconds, or something like that. That looked pretty strange to me but anyway, check it out for yourself and use whichever works better for your requirements. As for Java, yes, getTimeStamp() is what you need to use.
 
Dave Tolls
Ranch Foreman
Posts: 3056
37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As an Oracle user, the advice (and I could be out of date) is if you want time, then use a TIMESTAMP.
More to do with it being documented that the column is expecting a time element.

Yes, it is confusing. But then so is an empty VARCHAR being the same as NULL.
That's Oracle for you...

And, SQL Server should have no problem accepting the Timestamp directly with no additional effort.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!