Date Formatting Discrepencies between Table and Query 2

[Bonediggler1]

Hello

Long story short:

1) I pull field EXPORT_DATE (format DATE) from table TABLE1 which yields '9/10/2011 7:35:10 PM'

2) I need to take this value and plug it into the WHERE clause of another query, but Oracle expects this format: 'DD-MON-YY'.

3) I need to retain the timestamp

How do I reformat EXPORT_DATE into 'DD-MON-YY' format while including the timestamp?

Thank you!!

 

[carp]

If the source, code, and destination are all DATE datatypes, no conversion should be necessary.
If the source or the destination are actually character strings, then you would need to use the
to_date(source_data,'MM/DD/YYYY HH:MI:SS PM')
operator (if the source is a character string)
or the
to_char(destination_data,'DD-MON-YY HH:MI:SS')
operator (if the destination is a string).
If both source and destination are strings, then your easiest route would probably be to combine the two operators:
destination_data = to_char(to_date(source_data,'MM/DD/YYYY HH:MI:SS PM'),'DD-MON-YY HH:MI:SS PM')

Please post your source and destination table definitions (at least for the columns in question) and your code so that we can be more specific in our responses.

 

[Bonediggler1]

Thank you Carp

To be more specific:

1) Source and destination tables are both the same (TABLE1)
2) EXPORT_DATE in this table is datatype DATE
3) After pulling EXPORT_DATE in format 'MM/DD/YYYY HH:MI:SS PM' and putting it back into the query to get more data from TABLE1 I get the following error:

"Database Error: ORA-01843: not a valid month"

Table Def:

ID	Name	Datatype	Primary Key	Nullable	Data Default	Comments	Analyzed
49	EXPORT_DATE	DATE		Checked

 

[Bonediggler1]

Simple answer.

TO_DATE(EXPORT_DATE value,'MM/DD/YYYY HH:MI:SS AM')

Thanks for helping me think this through!

 

[SantaMufasa]

Simple answer.

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,

...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.

...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-digityear 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.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[sem]

Just a small elaboration (most probably needless but...)
Oracle SQL and PL/SQL DATE internal representations slightly differ, so don't be confused by that dump(sysdate) and dump(<date column>) show different lengths and moreover different types

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Alternatively to using explicit conversion you may alter default format mask on session (or even system) level by invoking

alter session set nls_date_format='<whatever you need>'

After such command you may rely on Oracle implicit conversion and be sure that data selected from date field and converted to text (varchar2) may be treated as date if needed.

Though I'd recommend you not to do this

Regards, Dima

http://seminihin.narod.ru

 

[SamBones]

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-digityear 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]

Hi Mufasa, I don't mean to find fault in your excellent response, but I believe the internal representation is seven bytes, not six. You list seven yourself.



I do agree that something is wrong in his approach if he's converting a date column to compare it with a date column.

 

[SantaMufasa]

Sam,

You are absolutely correct on each count...[ul][li]First, you should always correct a false assertion here on Tek-Tips...Correction keeps the quality high for our community. Neither others, nor I, should ever take offense at correction.[/li][li]Second, table-date expressions occupy seven bytes:

select dump(start_date) from s_emp;

Typ=12 Len=7: 120,190,3,3,9,31,1
...
Typ=12 Len=7: 119,191,5,9,1,1,1

25 rows selected.

...While (as Sem alluded earlier) SYSDATE occupies eight bytes (and is a different Oracle data type from table-stored DATEs: "12" versus "13"):

select dump(sysdate) from dual;

DUMP(SYSDATE)
----------------------------------
Typ=13 Len=8: 220,7,1,4,11,58,29,0

1 row selected.

[/li][/ul]Thanks, Sam, for posting the corrected information. Hava

!

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[SamBones]

Wow! A star for my proof reading prowess! Thank you!

I'll return a star for your excellent post. I always read your posts, Mufasa, because they are always full of the high quality technical information that I come here for.

On the OPs problem, I would like to see the code that produces the error...

Database Error: ORA-01843: not a valid month

If he's trying to self reference the same table, something is very wrong. Hard to debug that without the code.

 

[SantaMufasa]

Here are a scenario that generates the error that BoneDiggler1 is seeing:

select to_char(EXPORT_DATE,'MM/DD/YYYY HH:MI:SS PM') from exports;

02/07/1992 12:00:00 AM
03/08/1990 12:00:00 AM
02/09/1991 12:00:00 AM
08/06/1991 12:00:00 AM
07/21/1991 12:00:00 AM
05/26/1991 12:00:00 AM
11/30/1990 12:00:00 AM
10/17/1990 12:00:00 AM
03/17/1991 12:00:00 AM
05/09/1991 12:00:00 AM

10 rows selected.

select export_date from exports
 where to_date(to_char(EXPORT_DATE,'MM/DD/YYYY HH:MI:SS PM'),'DD/MM/YYYY HH:MI:SS PM') < SYSDATE;

ERROR:
ORA-01843: not a valid month

So, I'm certain that the error that BoneDiggler1 is seeing comes from the DATE-format mask not matching the character values coming in to the TO_DATE function.

Let us know BoneDiggler1 if that is, or is not, the case.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.