2

I have an Oracle Date type stored in the db in this format 06-MAR-20, when i mapped it to a java domain class i had a property of type

java.SQL.Date

but i got the data in this format 

2020-03-06

How can i stick to the exact same format from whats stored in my db? Will 

java.SQL.Timestamp

keep it the same?

Or do i need to do some configuration when getting the data from query to maintain its format?

GMB
  • 216,147
  • 25
  • 84
  • 135
user13342895
  • 23
  • 1
  • 3
  • 1
    I recommend you neither use `java.sql.Date` nor `java.sql.Timestamp`. Those classes are poorly designed and long outdated. Instead retrieve a `LocalDate` from your database. It’s from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). And as others have said, it cannot have a format, so don’t worry about that. – Ole V.V. Apr 18 '20 at 05:18

3 Answers3

4

tl;dr

I have an Oracle Date type stored in the db in this format 06-MAR-20

No, you don’t.

  • Databases such as Oracle store date-time values with their own internally defined binary values, not as plain text.
  • Oracle DATE type holds a time-of-day as well as a date.

DATE = date + time-of-day

The DATE type in Oracle database is misnamed. It holds more than a date (year, month, and day-of-month).

This type represents a date with time-of-day resolving to whole seconds. This type lacks the context of a time zone or offset-from-UTC. So this type cannot be used to represent a moment, a specific point on the timeline. (To track a moment, use Oracle type TIMESTAMP WITH TIME ZONE.)

Note that this Oracle naming differs from the SQL standard. In the standard, a DATE type is a date-only value, with no time-of-day and no time zone or offset.

java.time.LocalDateTime

So this type maps to LocalDateTime in Java.

Retrieval.

LocalDateTime ldt = myResultSet.getObject( … , LocalDateTime.class ) ;

Storage.

myPreparedStatement.setObject( … , ldt ) ;

Text

Notice that in the code above we are using smart objects rather than dumb strings.

Date-time objects such as LocalDateTime are not String and do not hold text. They internally represent their value in their own way. You can parse a string as a date-time object, and you can ask the date-time object to generate text. But the text and the date-time object are separate and distinct.

The java.time classes use standard ISO 8601 formats when parsing/generating strings.

String output = ldt.toString() ;

2020-01-23T01:23:45.123456789

Parsing.

You can generate text in other formats. You can specify your own custom format by calling DateTimeFormatter.ofPattern. Usually better to automatically format by calling DateTimeFormatter.ofLocalized… methods.

This has been covered many many times already on Stack Overflow. So search to learn more.

LocalDateTime ldt = LocalDateTime.parse( "2020-01-23T01:23:45.123456789" ) ;

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes. Hibernate 5 & JPA 2.2 support java.time.

Where to obtain the java.time classes?

Basil Bourque
  • 303,325
  • 100
  • 852
  • 1,154
0

Dates do not have a specific format. Internally, they are stored as a series of bytes that represent its year, month, day, hour, minutes, seconds.

What you are seeing when you look at your date in the database is a string representation whose format depends on nls setting nls_date_format of either your session (if it specified one), or of your database (if the parameter was not set at session level).

If you want to fetch your date in format DD-MON-YY, you can either use to_char() in your query:

to_char(mydatecol, 'DD-MON-YY')

Or you can set it at session level:

alter session set nls_date_format = 'DD-MON-YY';
GMB
  • 216,147
  • 25
  • 84
  • 135
  • okay im going to try used the to_date function, if i want the month to be capital do i need to add anything else? Also does this mean that the java field that is being mapped to should still be in java.SQL.Date? – user13342895 Apr 17 '20 at 20:18
  • @user13342895: sorry, typo: I meant `to_char()`. – GMB Apr 17 '20 at 20:18
  • @user13342895: that would be `DD-MON-YY`. – GMB Apr 17 '20 at 20:20
  • Okay, and if i wanted to assign the column a new name say ```select to_char(mydatecol, 'DD-MON-YY') as newColName from aTable;``` is this right or would the as newName be inside first param? – user13342895 Apr 17 '20 at 20:25
  • Okay and did you say i can keep the data type as java.SQL.Date? Would it work if it was timestamp as well? – user13342895 Apr 17 '20 at 20:26
  • @user13342895: I don't know a lot about Java, but I doubt that this maps to a Java date. In Oracle, `to_char()` returns a string. – GMB Apr 17 '20 at 20:29
  • Okay I will just map it to a string, thanks – user13342895 Apr 17 '20 at 20:31
  • 1
    *I will just map it to a string,* Bad idea, sorry. What do you need that format for? It we can understand, I am sure we can guide you better. – Ole V.V. Apr 18 '20 at 05:23
  • 1
    "Okay, and if i wanted to assign the column a new name say select to_char(mydatecol, 'DD-MON-YY') as newColName from aTable; is this right or would the as newName be inside first param? " Assigning a column alias is part of the SELECT syntax, It has nothing to do with TO_CHAR. You can see the doc on to_char at https://docs.oracle.com/database/121/SQLRF/functions216.htm#SQLRF06129. The nature of your question suggests you are just trying to feel your way through this without even consulting the docs. – EdStevens Apr 18 '20 at 13:12
0

I felt like you did not search the stackoverflow properly. There are lots of answers related to the problem you mentioned here. That will give you better idea how to tackle the problem.

The above answer can only be used if you are using straight JDBC, but usually enterprise apps will use some ORMs and most of then use it along with spring. Then you may not get the freedom to correct the sql everywhere and writing sql for every domain class is not recommended. So it is better to write custom methods for date properties that you have in your domain to get the date string in whatever format you want. Sample getStringDate(java.SQL.Date) to convert it to the format you want.

Date sqldate = new Date(new java.util.Date().getTime());
System.out.println(sqldate);
SimpleDateFormat format = new SimpleDateFormat("dd-MMM-yy");
System.out.println(format.format(sqldate));
SHAKU
  • 657
  • 5
  • 15
  • 3
    FYI, the terribly flawed date-time classes such as [`java.util.Date`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/util/Date.html), [`java.util.Calendar`](https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/util/Calendar.html), and `java.text.SimpleDateFormat` are now [legacy](https://en.wikipedia.org/wiki/Legacy_system), supplanted by the [*java.time*](https://docs.oracle.com/javase/tutorial/datetime/TOC.html) classes built into Java 8 and later. – Basil Bourque Apr 17 '20 at 22:01
  • 1
    If the OP needs a specific format in Java (I admit I am still not 100 % convinced, this may be due to a misunderstanding), you are correct that it’s better to format in Java. Using `SimpleDateFormat` for that is no good idea, though, it’s a notorious troublemaker of a class. Also I don’t think your code will capitalize the month abbreviation as requested. – Ole V.V. Apr 18 '20 at 05:26
  • I am just giving him a conceptual idea. I hate spoon feeding :) – SHAKU Apr 18 '20 at 23:10