BoneDiggler1 said:
Simple answer.
Code:
TO_DATE(EXPORT_DATE value,'MM/DD/YYYY HH:MI:SS AM')
Actually, that
is not the simple answer. The above answer is more complicated than it should be. You said that EXPORT_DATE is defined as DATE already. There is no need to "convert" EXPORT_DATE from DATE to DATE.
When you say,
BD said:
...but Oracle expects this format: 'DD-MON-YY'.
...what exactly do you mean by "...Oracle expects..."? If EXPORT_DATE is already a DATE, then the other operand (to which you are comparing EXPORT_DATE) needs attention. Can you please post the WHERE clause with which you are having the problem? Bottom line: DATE expressions should be both
stored and
compared as DATE expressions. If you need to convert a DATE expression to a character expression for the sake of comparison, then (with a very few exceptions) the comparison needs fixing.
BD said:
...I need to retain the timestamp
In Oracle, if a data item is of datatype DATE, then
it always, by definition, contains the TIME component. In fact,
you cannot get rid of the TIME component in an Oracle DATE expression. The most you can do in that regard is simply
ignore the TIME component.
Oracle DATE expressions are a six-byte internal representation of the following:[ul][li]A +/-
SIGN that represents
A.D. and
B.C. respectively.[/li][li]1 byte for a two-digit
century.[/li][li]1 Byte for a two-digit
year within the century.[/li][li]1 byte for a two-digit
month of the year.[/li][li]1 byte for a two-digit
day of the month.[/li][li]1 byte for a two-digit
hour of the day.[/li][li]1 byte for a two-digit
minute of the hour.[/li][li]1 byte for a two-digit
second of the minute.[/li][/ul](There is a separate format for Oracle's TIMESTAMP data type, but since you indicated that we are dealing with DATE data type, I won't go into TIMESTAMP data type, which stores DATEs and TIMEs down to the tiny fractions of a second.)
So, the bottom line is: In Oracle, store and compare DATEs (and their TIMEs) as datatype DATE...not as characters.
![[santa] [santa] [santa]](/data/assets/smilies/santa.gif)
Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.